PostgreSQL¶

The most feature-rich open-source relational database and the default choice for data platforms. Postgres adds powerful types (JSONB, arrays, ranges), full-text search, and mature analytics features on top of standard SQL.

Table of Contents¶

Installation / Setup
Core Syntax
Common Operations
Practical Examples
Common Patterns
Best Practices
Common Mistakes
Troubleshooting
Related Cheat Sheets
Official Documentation

Installation / Setup¶

# macOS:  brew install postgresql@16
# Ubuntu: sudo apt install postgresql
# Docker: docker run -d -e POSTGRES_PASSWORD=pass -p 5432:5432 postgres:16

psql -h localhost -U postgres -d mydb     # CLI

import psycopg2                            # classic driver
import pandas as pd
con = psycopg2.connect(host="localhost", dbname="mydb", user="postgres", password="pass")
pd.read_sql("SELECT * FROM orders", con)

psql command	Meaning
`\l`	list databases
`\c dbname`	connect to database
`\dt`	list tables
`\d table`	describe table (columns, indexes, FKs)
`\i file.sql`	run a SQL file
`\x`	expanded output (wide rows)
`\q`	quit

Core Syntax¶

Common data types¶

Type	Use
`INTEGER`, `BIGINT`, `SMALLINT`	integers
`NUMERIC(p, s)`	exact decimal (money!)
`REAL`, `DOUBLE PRECISION`	floats
`TEXT`, `VARCHAR(n)`	strings
`BOOLEAN`	true/false
`DATE`, `TIME`, `TIMESTAMP`, `TIMESTAMPTZ`	time (always prefer `timestamptz`)
`INTERVAL`	duration
`UUID`	globally unique ids
`JSONB`	binary JSON — queryable, indexable
`ARRAY`	native arrays (`TEXT[]`)
`SERIAL` / `GENERATED ALWAYS AS IDENTITY`	auto-increment
`ENUM`	fixed value lists

Dialect quick wins¶

-- String & case
SELECT name ILIKE '%ada%' FROM customers;       -- case-insensitive LIKE
SELECT SPLIT_PART(email, '@', 1) FROM customers;

-- Date math the Postgres way
SELECT NOW(), CURRENT_DATE,
       created_at + INTERVAL '30 days',
       DATE_TRUNC('week', created_at),
       EXTRACT(ISODOW FROM created_at);         -- 1=Mon .. 7=Sun

-- GREATEST/LEAST, DISTINCT ON, FILTER
SELECT GREATEST(a, b), LEAST(a, b) FROM t;

SELECT DISTINCT ON (customer_id) *              -- first row per key (no window needed)
FROM orders ORDER BY customer_id, created_at DESC;

SELECT department,
       COUNT(*) FILTER (WHERE salary > 100000) AS highly_paid,
       COUNT(*) AS total
FROM employees GROUP BY department;

Common Operations¶

JSONB (event/feature data without a schema)¶

CREATE TABLE events (id BIGINT, payload JSONB);

INSERT INTO events VALUES (1, '{"user_id": 42, "action": "click", "meta": {"page": "pricing"}}');

-- Operators
SELECT payload->>'user_id'            AS user_id,      -- -> text  |  -> json
       payload->'meta'->>'page'       AS page
FROM events;

-- Query inside JSON
SELECT * FROM events
WHERE payload->>'action' = 'click'
  AND payload @> '{"meta": {"page": "pricing"}}';

-- Index JSON fields
CREATE INDEX idx_events_action ON events ((payload->>'action'));
CREATE INDEX idx_events_gin ON events USING GIN (payload);

Full-text search¶

-- tsvector: precomputed search tokens; tsquery: the query
SELECT * FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('machine & learning');

CREATE INDEX idx_articles_fts ON articles
    USING GIN (to_tsvector('english', body));

Arrays¶

SELECT ARRAY[1, 2, 3], '{4,5,6}'::int[];
SELECT tags[1] FROM posts;                        -- 1-indexed
SELECT * FROM posts WHERE 'python' = ANY(tags);
SELECT unnest(tags) AS tag FROM posts;            -- expand to rows

Practical Examples¶

1. Time-series rollup to daily aggregates¶

WITH daily AS (
    SELECT user_id, DATE_TRUNC('day', created_at) AS day,
           COUNT(*) AS events
    FROM events GROUP BY 1, 2
)
SELECT day, AVG(events) AS avg_events_per_user,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY events) AS median_events
FROM daily GROUP BY day ORDER BY day;

2. Retention-style first/last with DISTINCT ON¶

SELECT DISTINCT ON (user_id)
       user_id, created_at AS first_seen
FROM events
ORDER BY user_id, created_at;

3. EXPLAIN a slow query¶

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, SUM(total) FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY customer_id;
-- look for: Seq Scan on big table (needs index), Hash vs Nested Loop,
-- actual vs estimated rows, and total time

4. Loading CSV with COPY (fastest bulk load)¶

COPY orders FROM '/tmp/orders.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

COPY (SELECT * FROM orders WHERE status = 'paid')
TO '/tmp/paid.csv' WITH (FORMAT csv, HEADER true);

Common Patterns¶

-- Upsert (insert or update on conflict)
INSERT INTO users (id, email, name)
VALUES (1, 'a@x.com', 'Ada')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

-- Keep a denormalized summary fresh with a trigger-safe view or materialized view
CREATE MATERIALIZED VIEW mv_monthly AS
    SELECT DATE_TRUNC('month', created_at) AS month, SUM(total) AS revenue
    FROM orders GROUP BY 1;
REFRESH MATERIALIZED VIEW mv_monthly;           -- re-run when needed

-- Efficient pagination (keyset, not OFFSET)
SELECT * FROM orders
WHERE (created_at, id) < ('2024-05-01', 1000)   -- last seen cursor
ORDER BY created_at DESC, id DESC LIMIT 100;

Best Practices¶

Use TIMESTAMPTZ everywhere; store UTC, convert at display time.
Prefer NUMERIC for money, DOUBLE PRECISION for measurements.
Index hot paths: join keys, WHERE columns, and JSONB accessors with expression indexes.
Use EXPLAIN (ANALYZE) before declaring a query slow — guess less, measure.
Prefer DISTINCT ON / window functions over self-joins for per-group first/last.
Batch with COPY for bulk loads instead of row-by-row INSERTs.
JSONB for flexible payloads, but promote hot fields to real columns — JSONB is not a substitute for schema.
Always check pg_stat_activity and vacuum/autovacuum status on write-heavy systems.

Common Mistakes¶

Mistake	Consequence	Fix
`TIMESTAMP` instead of `TIMESTAMPTZ`	Wrong times across time zones	Use `timestamptz`
`WHERE jsonb_col = '{"a":1}'`	Never matches (binary form)	`@>`, `->>`, or `#>>` operators
OFFSET pagination on huge tables	Slows as pages grow	Keyset pagination
Missing index on `lower(email)` lookups	Full scan	`CREATE INDEX ON t (lower(email))`
`ON CONFLICT DO NOTHING` when you want update	Silently drops rows	`DO UPDATE SET ...`
Assuming VARCHAR(n) saves space	No benefit over TEXT in Postgres	Use TEXT
Updating a JSONB column wholesale	Wasted writes	Set subfield via `jsonb_set`

Troubleshooting¶

Symptom	Cause	Fix
`connection refused`	Server down / wrong port	`pg_isready`; check port 5432 and pg_hba
`relation does not exist`	Wrong schema/search_path	`SET search_path TO myschema, public`
`duplicate key value violates unique constraint`	Upsert conflict handled wrong	Use `ON CONFLICT`
Slow query suddenly	Stale planner stats	`ANALYZE table;` then re-EXPLAIN
`out of shared memory`	Low `shared_buffers` settings	Tune postgresql.conf; restart
Lock waits (`pg_locks`)	Long transaction holding locks	Inspect `pg_stat_activity`; commit/rollback

sql.md — the standard SQL foundation
MySQL — the other major open-source RDBMS
SQLite — lightweight embedded alternative
pandas.md — pull results into DataFrames
Data Engineering — Postgres in pipelines/warehouses
Home · Central Navigation Index

Official Documentation¶

Last Updated¶

2026-09-06 — Initial version.

PostgreSQL¶

Table of Contents¶

Installation / Setup¶

Core Syntax¶

Common data types¶

Dialect quick wins¶

Common Operations¶

JSONB (event/feature data without a schema)¶

Full-text search¶

Arrays¶

Practical Examples¶

1. Time-series rollup to daily aggregates¶

2. Retention-style first/last with DISTINCT ON¶

3. EXPLAIN a slow query¶

4. Loading CSV with COPY (fastest bulk load)¶

Common Patterns¶

Best Practices¶

Common Mistakes¶

Troubleshooting¶

Related Cheat Sheets¶

Official Documentation¶

Last Updated¶