PostgreSQL Cheat Sheet
Quick reference for PostgreSQL. Connection, CRUD, joins, indexes, JSON, window functions, and administration — all in one page.
Connection & psql
CRUD Operations
Joins
Indexes & Performance
JSON / JSONB
Window Functions
Common Types
Administration
Connection & psql
| psql -U user -d dbname -h host | Connect to database |
| \l | List databases |
| \c dbname | Switch database |
| \dt | List tables in current schema |
| \d tablename | Describe table (columns, types, indexes) |
| \di | List indexes |
| \df | List functions |
| \dn | List schemas |
| \q | Quit psql |
| \timing | Toggle query execution time display |
CRUD Operations
| SELECT * FROM users WHERE active = true | Basic query with filter |
| SELECT name, COUNT(*) FROM orders GROUP BY name | Aggregate with grouping |
| INSERT INTO users (name, email) VALUES ('John', '[email protected]') | Insert row |
| INSERT INTO users (name) VALUES ('A'), ('B'), ('C') | Insert multiple rows |
| UPDATE users SET active = false WHERE last_login < NOW() - INTERVAL '1 year' | Update with condition |
| DELETE FROM sessions WHERE expires_at < NOW() | Delete expired rows |
| UPSERT: INSERT ... ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name | Insert or update on conflict |
Joins
| SELECT * FROM a INNER JOIN b ON a.id = b.a_id | Inner join (matching rows only) |
| SELECT * FROM a LEFT JOIN b ON a.id = b.a_id | Left join (all from a, matching from b) |
| SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id | Right join (all from b, matching from a) |
| SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id | Full join (all from both) |
| SELECT * FROM a CROSS JOIN b | Cross join (cartesian product) |
| SELECT * FROM a JOIN b USING (id) | Join using shared column name |
Indexes & Performance
| CREATE INDEX idx_name ON users(email) | B-tree index (default) |
| CREATE UNIQUE INDEX idx_name ON users(email) | Unique index |
| CREATE INDEX idx_name ON logs USING GIN(tags) | GIN index (arrays, JSON, full-text) |
| CREATE INDEX idx_name ON points USING GIST(location) | GiST index (geometric, spatial) |
| CREATE INDEX CONCURRENTLY idx_name ON big_table(col) | Create without locking table |
| EXPLAIN ANALYZE SELECT ... | Show query execution plan with timing |
| VACUUM ANALYZE tablename | Update statistics and reclaim space |
JSON / JSONB
| data->'key' | Get JSON value (as JSON) |
| data->>'key' | Get JSON value (as text) |
| data#>'{a,b}' | Get nested JSON value |
| data @> '{"status":"active"}' | Contains (JSONB only) |
| jsonb_set(data, '{key}', '"value"') | Set value in JSONB |
| jsonb_agg(column) | Aggregate rows into JSON array |
| jsonb_each(data) | Expand JSONB to key-value rows |
Window Functions
| ROW_NUMBER() OVER (ORDER BY created_at) | Sequential row number |
| RANK() OVER (PARTITION BY dept ORDER BY salary DESC) | Rank within group (gaps on ties) |
| DENSE_RANK() OVER (...) | Rank without gaps |
| LAG(value, 1) OVER (ORDER BY date) | Previous row value |
| LEAD(value, 1) OVER (ORDER BY date) | Next row value |
| SUM(amount) OVER (PARTITION BY user_id) | Running sum per group |
| AVG(score) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) | 7-day moving average |
Common Types
| SERIAL / BIGSERIAL | Auto-incrementing integer |
| UUID | Universally unique identifier (use gen_random_uuid()) |
| TEXT / VARCHAR(n) | Variable-length string |
| TIMESTAMP WITH TIME ZONE | Date + time + timezone (use this, not WITHOUT) |
| JSONB | Binary JSON (indexable, faster than JSON) |
| BOOLEAN | true / false / null |
| INTEGER[] / TEXT[] | Array types |
| NUMERIC(10,2) | Exact decimal (use for money) |
Administration
| CREATE DATABASE mydb | Create database |
| CREATE USER dev WITH PASSWORD 'pass' | Create user |
| GRANT ALL ON DATABASE mydb TO dev | Grant permissions |
| ALTER TABLE users ADD COLUMN phone TEXT | Add column |
| ALTER TABLE users ALTER COLUMN name SET NOT NULL | Add NOT NULL constraint |
| pg_dump -U user dbname > backup.sql | Backup database |
| psql -U user dbname < backup.sql | Restore database |
| SELECT pg_size_pretty(pg_database_size('dbname')) | Database size |
Try It Live
Test these patterns with our free SQL Formatter. No signup needed.
Open SQL Formatter →
Step-by-Step Guide
Read Guide →