Cheatsheet
SQL syntax you look up mid query
Postgres flavored, grouped by what you are trying to write: filtering, joins, aggregates, window functions, upserts, and the JSON operators nobody memorizes. Most of it is standard SQL and works unchanged in MySQL and SQLite; the Postgres only parts are marked.
Selecting and filtering rows
| Syntax | What it does |
|---|---|
| WHERE status = 'active' | Equality. Single quotes are for values; double quotes are for identifiers. |
| WHERE price BETWEEN 10 AND 20 | Inclusive range on both ends. |
| WHERE id IN (1, 2, 3) | Set membership. Also accepts a subquery. |
| WHERE deleted_at IS NULL | The only correct null test. Equals null is never true. |
| WHERE a IS DISTINCT FROM b | Null safe inequality: treats two nulls as equal rather than unknown. |
| WHERE email ILIKE '%@gmail.com' | Case insensitive pattern match. Postgres only; use LOWER elsewhere. |
| WHERE name ~ '^A[a-z]+' | Regex match in Postgres. Use ~* for the case insensitive version. |
| ORDER BY created_at DESC NULLS LAST | Control where nulls sort. Postgres puts them first on DESC by default. |
| LIMIT 20 OFFSET 40 | Page three of twenty. Slow on large offsets; prefer keyset pagination. |
| WHERE (created_at, id) < ($1, $2) | Keyset pagination with a row comparison. Stays fast at any depth. |
| SELECT DISTINCT ON (user_id) * | One row per user_id, picked by ORDER BY. Postgres only, and very fast. |
| COALESCE(nickname, name, 'anon') | First non null argument. |
| NULLIF(count, 0) | Returns null when the two arguments match. The standard divide by zero guard. |
| CASE WHEN n > 9 THEN 'hi' ELSE 'lo' END | Inline conditional. Works in SELECT, WHERE, ORDER BY, and GROUP BY. |
Gotcha: NOT IN with a subquery that can return null matches nothing at all, silently. Use NOT EXISTS instead, every time.
Joins
| Syntax | Keeps |
|---|---|
| INNER JOIN b ON b.a_id = a.id | Only rows with a match on both sides. Plain JOIN means this. |
| LEFT JOIN b ON b.a_id = a.id | Every row of a, with nulls where b has no match. |
| RIGHT JOIN b ON b.a_id = a.id | Every row of b. Rare in practice: flip the tables and use LEFT. |
| FULL OUTER JOIN b ON b.a_id = a.id | Every row of both sides, nulls filling the gaps. Good for reconciliation. |
| CROSS JOIN b | Cartesian product. Useful with generate_series, dangerous by accident. |
| JOIN b USING (tenant_id) | Shorthand when the column names match. Emits the column only once. |
| LEFT JOIN LATERAL (...) l ON true | Subquery that can reference the outer row. The clean top N per group. |
| WHERE EXISTS (SELECT 1 FROM b ...) | Semi join: filters without duplicating rows or widening the result. |
| WHERE NOT EXISTS (SELECT 1 FROM b ...) | Anti join: rows in a with nothing matching in b. Null safe. |
| a UNION b / a UNION ALL b | Stack result sets. UNION deduplicates and sorts; UNION ALL does not, so it is faster. |
| a INTERSECT b / a EXCEPT b | Rows in both, or rows in the first but not the second. |
Gotcha: putting a condition on the right hand table in the WHERE clause of a LEFT JOIN quietly turns it into an inner join, because the null rows fail the test. Put that condition in the ON clause instead.
Aggregating and grouping
| Syntax | What it does |
|---|---|
| COUNT(*) | Rows in the group, nulls included. |
| COUNT(col) | Rows where col is not null. A frequent source of off by many bugs. |
| COUNT(DISTINCT user_id) | Unique non null values. |
| COUNT(*) FILTER (WHERE paid) | Conditional aggregate without a CASE. Postgres and SQLite support it. |
| SUM(amount), AVG(amount) | Total and mean, both ignoring nulls. |
| MIN(x), MAX(x) | Extremes. Work on text and timestamps, not just numbers. |
| STRING_AGG(name, ', ' ORDER BY name) | Concatenate a group into one delimited string, in a defined order. |
| ARRAY_AGG(id ORDER BY id) | Collect a group into a Postgres array. |
| JSON_AGG(row_to_json(t)) | Build nested JSON in the database instead of stitching it in the app. |
| PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY ms) | True interpolated p95. The honest latency number. |
| GROUP BY 1, 2 | Group by output column position. Handy for ad hoc queries, poor in code. |
| GROUP BY ROLLUP (region, city) | Adds subtotal and grand total rows. CUBE gives every combination. |
| HAVING COUNT(*) > 5 | Filter groups after aggregation. WHERE filters rows before it. |
Gotcha: logical evaluation order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. That is why a SELECT alias is invisible to WHERE but usable in ORDER BY.
Window functions
A window function computes across a set of rows without collapsing them, which is the whole point: you keep every row and gain a ranking, a running total, or the previous row's value.
| Function | Returns |
|---|---|
| ROW_NUMBER() OVER (ORDER BY score DESC) | 1, 2, 3 with no ties ever. Arbitrary tiebreak unless you add one. |
| RANK() OVER (ORDER BY score DESC) | Ties share a rank and the next value skips: 1, 1, 3. |
| DENSE_RANK() OVER (ORDER BY score DESC) | Ties share a rank and nothing is skipped: 1, 1, 2. |
| NTILE(4) OVER (ORDER BY score) | Bucket number when the ordered rows are split into four equal groups. |
| LAG(amount, 1, 0) OVER (ORDER BY day) | The previous row's value, with a default when there is none. |
| LEAD(amount) OVER (ORDER BY day) | The next row's value. Pair with LAG for day over day deltas. |
| FIRST_VALUE(x) OVER (PARTITION BY g ORDER BY t) | The first value in the partition, repeated on every row of it. |
| NTH_VALUE(x, 2) OVER (...) | The nth value in the frame. |
| SUM(amt) OVER (ORDER BY day) | Running total, because an ORDER BY implies a frame ending at the current row. |
| SUM(amt) OVER () | Grand total on every row, for computing a percentage of the whole. |
| AVG(x) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) | Seven day moving average, defined by an explicit frame. |
| PERCENT_RANK() OVER (ORDER BY score) | Relative rank from 0 to 1. CUME_DIST gives the cumulative distribution. |
| WINDOW w AS (PARTITION BY g ORDER BY t) | Name a window once, then write OVER w on as many functions as you like. |
-- Latest order per customer, plus its rank and the previous order total
SELECT *
FROM (
SELECT
o.*,
ROW_NUMBER() OVER w AS recency,
LAG(o.total) OVER w AS previous_total
FROM orders o
WINDOW w AS (PARTITION BY o.customer_id ORDER BY o.created_at DESC)
) ranked
WHERE recency = 1;Gotcha: window functions run after WHERE and GROUP BY, so you cannot filter on one directly. Wrap the query in a subquery or a CTE, as above. Note also that the default frame with an ORDER BY is RANGE based, which treats tied rows as one unit; use ROWS when you mean literal rows.
Inserting, updating, and upserting
| Syntax | What it does |
|---|---|
| INSERT INTO t (a, b) VALUES ($1, $2) | Parameterized insert. Never build SQL by string concatenation. |
| INSERT INTO t (a) VALUES (1), (2), (3) | Multi row insert. Far faster than three round trips. |
| INSERT INTO t (a) SELECT x FROM other | Insert the result of a query, without a round trip through the app. |
| ... RETURNING id, created_at | Get generated values back from an insert, update, or delete. Postgres. |
| ON CONFLICT (email) DO NOTHING | Skip rows that violate a unique constraint instead of erroring. |
| ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name | Upsert. EXCLUDED holds the row you tried to insert. |
| UPDATE t SET a = a + 1 WHERE id = $1 | Atomic increment in the database. Read modify write in the app races. |
| UPDATE t SET x = s.x FROM src s WHERE s.id = t.id | Update joined to another table. Postgres spells it UPDATE ... FROM. |
| DELETE FROM t WHERE id = $1 | Row delete. Logged, transactional, and revertible inside a transaction. |
| TRUNCATE t RESTART IDENTITY CASCADE | Empty a table fast and reset its sequences. Cascades to referencing tables. |
| SELECT ... FOR UPDATE SKIP LOCKED | Claim rows for this worker only. The core of a job queue in Postgres. |
| BEGIN; ... COMMIT; / ROLLBACK; | Transaction boundaries. SAVEPOINT gives you a partial rollback point. |
Gotcha: run every destructive statement as a SELECT with the same WHERE clause first. An UPDATE or DELETE without a WHERE clause is valid SQL and hits every row.
CTEs, JSON, and Postgres specifics
| Syntax | What it does |
|---|---|
| WITH recent AS (SELECT ...) SELECT * FROM recent | Common table expression: a named subquery that reads top to bottom. |
| WITH RECURSIVE tree AS (...) | Walk a hierarchy: base case, UNION ALL, then the step that references itself. |
| WITH moved AS (DELETE FROM a RETURNING *) INSERT INTO b SELECT * FROM moved | Writable CTE: move rows between tables in one statement. |
| data -> 'user' ->> 'name' | JSON navigation. Single arrow returns JSON, double arrow returns text. |
| data #>> '{user,address,city}' | Deep path lookup returning text, using an array path. |
| data @> '{"role":"admin"}' | Containment test on jsonb. Indexable with a GIN index. |
| jsonb_build_object('id', id, 'name', name) | Construct JSON from columns, keys and values alternating. |
| jsonb_array_elements(data -> 'items') AS item | Expand a JSON array into rows so you can join or aggregate it. |
| tags @> ARRAY['sql'] | Array containment. ANY and ALL also work against array columns. |
| generate_series('2026-01-01'::date, now(), '1 day') | Produce a dense date spine so a report has rows for empty days. |
| date_trunc('week', created_at) | Bucket timestamps for time series grouping. |
| now() - INTERVAL '7 days' | Interval arithmetic on timestamps. |
| to_tsvector('english', body) @@ plainto_tsquery('cat') | Built in full text search, no external engine needed. |
Gotcha: use jsonb, not json, for anything you will query. The json type stores the raw text and cannot be indexed usefully. Also note that CTEs stopped being an optimization fence in Postgres 12; add MATERIALIZED if you actually wanted one.
Schema and performance
| Statement | What it does |
|---|---|
| EXPLAIN ANALYZE SELECT ... | Runs the query and prints the real plan with actual row counts and timings. |
| EXPLAIN (ANALYZE, BUFFERS) SELECT ... | Adds cache and disk block counts, which is where the real cost hides. |
| CREATE INDEX CONCURRENTLY idx ON t (col) | Build an index without locking writes. Mandatory on a live table. |
| CREATE UNIQUE INDEX ON t (lower(email)) | Expression index. Enforces case insensitive uniqueness. |
| CREATE INDEX ON t (a, b) WHERE deleted_at IS NULL | Partial composite index: smaller, and matches your real query shape. |
| CREATE INDEX ON t USING GIN (data jsonb_path_ops) | Index jsonb containment queries. |
| ALTER TABLE t ADD COLUMN c text | Adding a nullable column is instant. Adding NOT NULL without a default is not. |
| ALTER TABLE t ADD CONSTRAINT fk ... NOT VALID | Add a constraint without scanning the table; validate it later, separately. |
| id bigint GENERATED ALWAYS AS IDENTITY | The modern primary key. Prefer it over the legacy serial type. |
| created_at timestamptz NOT NULL DEFAULT now() | Always timestamptz, never timestamp. Timezone free columns cause outages. |
| ANALYZE t | Refresh planner statistics after a bulk load, or plans will be wrong. |
| SELECT * FROM pg_stat_activity WHERE state = 'active' | What is running right now, including who is blocking whom. |
Gotcha: a composite index on (a, b) serves queries filtering on a or on both, but not queries filtering on b alone. Column order is the whole design decision.
Keep going
Still choosing an engine? Read Postgres vs SQLite and MySQL vs Postgres, then browse the database section of the tool directory for hosted options.
Running Postgres locally is a four line Compose service, covered in the Docker cheatsheet. More references live in the cheatsheet index.