PostgreSQL Cheat Sheet
Core PostgreSQL commands covering psql navigation, SQL queries, indexing, and common data types for building and tuning relational databases.
2 PagesIntermediateMar 8, 2026
psql Basics
Connecting and navigating with the psql CLI.
bash
psql -U user -d mydb -h localhost # Connect\l # List databases\c mydb # Connect to a database\dt # List tables\d users # Describe a table\du # List roles\timing # Toggle query timing\q # Quit
SQL Essentials
Common DDL and DML statements.
sql
CREATE TABLE users ( id SERIAL PRIMARY KEY, email TEXT UNIQUE NOT NULL, created_at TIMESTAMPTZ DEFAULT now());INSERT INTO users (email) VALUES ('[email protected]') RETURNING id;SELECT * FROM users WHERE email ILIKE '%example%';UPDATE users SET email = '[email protected]' WHERE id = 1;WITH recent AS ( SELECT * FROM orders WHERE created_at > now() - interval '7 days')SELECT * FROM recent;
Indexes & EXPLAIN
Creating indexes and reading query plans.
sql
CREATE INDEX idx_users_email ON users(email);CREATE UNIQUE INDEX idx_users_email_uniq ON users(email);CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);EXPLAIN ANALYZESELECT * FROM users WHERE email = '[email protected]';
Common Data Types
Frequently used PostgreSQL column types.
- SERIAL- auto-incrementing 4-byte integer, backed by a sequence
- JSONB- binary JSON storage that supports indexing and containment queries
- UUID- 128-bit universally unique identifier
- TEXT- variable-length string with no length limit
- TIMESTAMPTZ- timestamp stored with timezone awareness
- NUMERIC(p,s)- exact fixed-precision decimal number
- ARRAY- e.g. INTEGER[], a native array column type
- BOOLEAN- true/false/null value
Admin & Maintenance
Backup, privileges, and housekeeping.
- VACUUM ANALYZE- reclaims dead tuple space and refreshes planner statistics
- pg_dump- exports a database or table to a backup file
- pg_restore- restores a database from a pg_dump archive
- GRANT / REVOKE- adds or removes privileges on objects for a role
- REINDEX- rebuilds a corrupted or bloated index
Window Functions & Recursive CTEs
Rank rows within groups and walk hierarchical data without round-tripping to the app.
sql
-- Rank orders per customer by amount, and show the running totalSELECT customer_id, amount, RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rank, SUM(amount) OVER (PARTITION BY customer_id ORDER BY created_at) AS running_totalFROM orders;-- Recursive CTE: walk an org chart from a root employee downWITH RECURSIVE org AS ( SELECT id, manager_id, name, 1 AS depth FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.manager_id, e.name, org.depth + 1 FROM employees e JOIN org ON e.manager_id = org.id)SELECT * FROM org ORDER BY depth;
LATERAL Joins
Run a correlated subquery per row, e.g. 'top N per group' without a window function.
sql
-- Get each customer's 3 most recent ordersSELECT c.id AS customer_id, o.*FROM customers cCROSS JOIN LATERAL ( SELECT id, amount, created_at FROM orders WHERE orders.customer_id = c.id ORDER BY created_at DESC LIMIT 3) o;
Declarative Table Partitioning
Split a large table by range so old partitions can be dropped instantly instead of DELETEd.
sql
CREATE TABLE events ( id BIGSERIAL, created_at TIMESTAMPTZ NOT NULL, payload JSONB) PARTITION BY RANGE (created_at);CREATE TABLE events_2026_01 PARTITION OF events FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');CREATE TABLE events_2026_02 PARTITION OF events FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');-- Instant, near-zero-cost delete of a whole monthDROP TABLE events_2026_01;
JSONB Operators & GIN Indexing
Query semi-structured columns efficiently with containment and path operators.
sql
-- Containment: does payload include this key/value?SELECT * FROM events WHERE payload @> '{"type": "signup"}';-- Path extractionSELECT payload -> 'user' ->> 'email' AS email FROM events;-- Key existenceSELECT * FROM events WHERE payload ? 'error_code';-- Index that makes @>, ?, ?| and ?& fast on this columnCREATE INDEX idx_events_payload_gin ON events USING GIN (payload jsonb_path_ops);
Transaction Isolation Levels
Postgres's MVCC-backed isolation levels and the anomalies each one prevents.
- READ COMMITTED- Default; each statement sees a fresh snapshot, so a transaction can see other transactions' commits mid-way
- REPEATABLE READ- One snapshot for the whole transaction; prevents non-repeatable reads and phantom reads via row versioning
- SERIALIZABLE- Detects write skew and other serialization anomalies, aborting one transaction with a retryable error
- MVCC- Readers never block writers and vice versa; each row version is tagged with the transaction that created it
- Advisory locks- pg_advisory_lock()/pg_advisory_unlock() let the app coordinate on an arbitrary integer key outside row locks
Pro Tip
Use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN — it shows actual row counts and buffer hits/misses, not just planner estimates, which is what you need to diagnose a slow query.
Was this cheat sheet helpful?
Explore Topics
#PostgreSQL#PostgreSQLCheatSheet#Database#Intermediate#PsqlBasics#SQLEssentials#IndexesEXPLAIN#CommonDataTypes#Databases#CheatSheet#SkillVeris
