00_postgresql.md · GitHub
Skip to content

Instantly share code, notes, and snippets.

@sadhik-ali

sadhik-ali/00_postgresql.md

Last active August 16, 2026 06:46
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save sadhik-ali/5c6a75f2415c8ccb1146383dc1c3c289 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save sadhik-ali/5c6a75f2415c8ccb1146383dc1c3c289 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
00_postgresql.md

PostgreSQL

PostgreSQL, often referred to as Postgres, stands out as a leading open-source relational database management system (RDBMS) renowned for its robust features and flexibility. Its benefits span various aspects crucial for modern data management.

PostgreSQL excels in scalability, accommodating large volumes of data while maintaining performance. Its architecture supports multi-version concurrency control (MVCC), enabling efficient handling of concurrent transactions without compromising data integrity. Additionally, PostgreSQL offers a wide array of data types, including JSON and XML, facilitating the storage and manipulation of diverse data formats.

Furthermore, PostgreSQL prioritizes extensibility, allowing users to customize and extend its functionality through procedural languages like PL/pgSQL and numerous built-in and third-party extensions. This extensibility empowers developers to tailor PostgreSQL to suit specific project requirements, enhancing its versatility.

Data security is paramount in PostgreSQL, with features such as robust authentication mechanisms, SSL support, and encryption options ensuring data confidentiality and integrity. Moreover, PostgreSQL's active community fosters continuous development, providing regular updates, patches, and support resources, guaranteeing reliability and longevity for users.

In conclusion, PostgreSQL offers a compelling solution for organizations seeking a powerful, flexible, and secure RDBMS. Its scalability, extensibility, and commitment to data integrity make it a preferred choice for a wide range of applications, from small-scale projects to enterprise-level systems.

Depending how new you are to PostgreSQL, you can dive into the documentation, or just try official tutorial.

PostgreSQL Features

Data Types

  • Primitives: Integer, Numeric, String, Boolean
  • Structured: Date/Time, Array, Range / Multirange, UUID
  • Document: JSON/JSONB, XML, Key-value (Hstore)
  • Geometry: Point, Line, Circle, Polygon
  • Customizations: Composite, Custom Types

Data Integrity

  • UNIQUE, NOT NULL constraints
  • Primary Keys
  • Foreign Keys
  • Exclusion Constraints
  • Explicit Locks, Advisory Locks

Concurrency, Performance

  • Indexing: B-tree, Multicolumn, Expressions, Partial
  • Advanced Indexing: GiST, SP-Gist, KNN Gist, GIN, BRIN, Covering indexes, Bloom filters
  • Sophisticated query planner / optimizer, index-only scans, multicolumn statistics
  • Transactions, Nested Transactions (via savepoints)
  • Multi-Version concurrency Control (MVCC)
  • Parallelization of read queries and building B-tree indexes
  • Table partitioning
  • All transaction isolation levels defined in the SQL standard, including Serializable
  • Just-in-time (JIT) compilation of expressions

Reliability, Disaster Recovery

  • Write-ahead Logging (WAL)
  • Replication: Asynchronous, Synchronous, Logical
  • Point-in-time-recovery (PITR), active standbys
  • Tablespaces

Security

  • Authentication: GSSAPI, SSPI, LDAP, SCRAM-SHA-256, Certificate, and more
  • Robust access-control system
  • Column and row-level security
  • Multi-factor authentication with certificates and an additional method

Extensibility

  • Stored functions and procedures
  • Procedural Languages: PL/pgSQL, Perl, Python, Tcl, Java, JavaScript (V8), R, Lua, Rust
  • SQL/JSON constructors and path expressions
  • Foreign data wrappers: connect to other databases or streams with a standard SQL interface
  • Customizable storage interface for tables
  • Many extensions that provide additional functionality, including PostGIS

Internationalisation, Text Search

  • Support for international character sets, e.g., through ICU collations
  • Case-insensitive and accent-insensitive collations
  • Full-text search
Raw
01_psql.md 
# Connect to PostgreSQL
psql -U postgres

# List all databases
\l

# Connect to a specific database
\c mydatabase

# List all tables in the connected database
\d

# List details of a specific table
\d tablename
Raw
02_basicoperations.md

Basic Operations

# Create Database:
CREATE DATABASE mydatabase;
# Create Table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
# Insert Data
INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com');
# Select Data
SELECT * FROM users;
Raw
03_intermediateoperations.md

Intermediate Operations

# Update Data
UPDATE users SET email = 'newemail@example.com' WHERE id = 2;
# Delete Data
DELETE FROM users WHERE id = 1;
# Add Column to Table
ALTER TABLE users ADD COLUMN age INT;
# Drop Column from Table
ALTER TABLE users DROP COLUMN age;
Raw
04_advancedoperations.md

Advanced Operations

# Create Index
CREATE INDEX idx_username ON users (username);
# Create View
CREATE VIEW active_users AS
SELECT * FROM users WHERE active = true;
# Create Function
CREATE FUNCTION get_user_count() RETURNS INTEGER AS $$
DECLARE
    count INTEGER;
BEGIN
    SELECT COUNT(*) INTO count FROM users;
    RETURN count;
END;
$$ LANGUAGE plpgsql;
# Create Trigger
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_users_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();
# Create Table Partitioning
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    sale_date DATE,
    amount DECIMAL
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2022 PARTITION OF sales
FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

CREATE TABLE sales_2023 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment