SQL & PostgreSQL cheat sheet

SELECT name, price FROM products
WHERE category = 'bags' AND stock > 0
ORDER BY price DESC LIMIT 2 OFFSET 2;

-- keyset paging: fast at any depth
WHERE id > :last_id ORDER BY id LIMIT 20;

• false AND NULL is false, true OR NULL is true, the rest stay unknown. WHERE keeps only true.
• coalesce(a, b) first non-NULL. float8 is approximate: 0.1+0.2 gives 0.30000000000000004.

INSERT INTO customers (name, email)
  VALUES ('Gus', 'g@x.com') RETURNING id;
UPDATE products SET stock = stock - 1
  WHERE name = 'Pen' RETURNING stock;
INSERT ... ON CONFLICT (email) DO NOTHING;

Watch out: UPDATE or DELETE with no WHERE hits every row. Run the same WHERE as a SELECT first. Failed inserts still use identity numbers, so ids have gaps.

-- customers with no orders
SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
  WHERE o.customer_id = c.id);
-- self join: referrer name
FROM customers c
LEFT JOIN customers r
  ON r.id = c.referred_by

• A join gives one row per matching pair. A one-to-many join repeats the one side, so summing it double counts.
• LEFT JOIN then WHERE on a right column turns it into an inner join. Put right-table filters in ON.

• count(*) rows. count(col) non-NULL. count(DISTINCT col) different values. Other aggregates ignore NULL.
• After a LEFT JOIN, count a right-table column: count(o.id), not count(*).
• WHERE filters rows, HAVING filters groups. Every selected column is grouped or aggregated.

SELECT customer_id, count(*) AS orders,
  count(*) FILTER (WHERE status='paid')
    AS paid
FROM orders GROUP BY customer_id
HAVING count(*) >= 2;
-- monthly report
GROUP BY date_trunc('month', ordered_at)

1. FROM / JOIN, then WHERE, then GROUP BY and aggregates.
2. HAVING, then SELECT (aliases born here), then DISTINCT.
3. ORDER BY, then LIMIT.

• Scalar subquery: one row, one column. Two rows is an error, no row is NULL.
• IN and EXISTS plan the same. Use NOT EXISTS, never NOT IN, with nullable data.
• CTE used once is inlined (PostgreSQL 12+). Used twice it is computed once. Force with MATERIALIZED or NOT MATERIALIZED.

WITH RECURSIVE tree AS (
  SELECT id, name, 0 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, t.depth + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
) SELECT * FROM tree;

SELECT * FROM (
  SELECT customer_id, total,
    row_number() OVER (
      PARTITION BY customer_id
      ORDER BY total DESC) AS rn
  FROM ot) x WHERE rn <= 3;
-- running total, explicit frame
sum(total) OVER (ORDER BY id ROWS BETWEEN
  UNBOUNDED PRECEDING AND CURRENT ROW)

• Windows keep every row. They are not allowed in WHERE: wrap in a subquery.
• lag(x) / lead(x) read neighbours. Default frame includes ties, so last_value returns the current row.

• 1NF one value per cell. 2NF every column depends on the whole key. 3NF on the key and nothing else.
• Constraints: NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY. A CHECK passes on NULL, so add NOT NULL.
• Many-to-many needs a junction table with a composite key. Index the other direction.

• Use GENERATED ALWAYS AS IDENTITY, not serial: it refuses manual ids.
• Money: numeric(p,s) or integer cents (price_cents). 1999/100 is 19: use / 100.0.
• timestamptz stores one moment (UTC) and shows it in the session zone. Pick one zone for reports.
• Soft delete: partial unique index ON (lower(email)) WHERE deleted_at IS NULL, plus a view. Every query needs the filter.

-- equality columns first, then range
CREATE INDEX ON orders
  (customer_id, status);
-- partial: only the rows you query
CREATE INDEX ON orders (ordered_at)
  WHERE status = 'new';
-- covering
CREATE INDEX ON orders (customer_id)
  INCLUDE (amount_cents);

• Index (a, b) serves a and a AND b, not b alone. It makes (a) redundant.
• Not used when: you need most rows, WHERE id::text = .. or col + 0 (function on the column), LIKE '%x', table tiny, stale stats.
• Every write pays: 4 extra indexes made a load 11 times slower. Check idx_scan = 0 in pg_stat_user_indexes.

• BEGIN / COMMIT / ROLLBACK: all or nothing. After an error the transaction is aborted until ROLLBACK. SAVEPOINT undoes part.
• Lost update: read, compute in the app, write. Fix: atomic SET stock = stock - 4 WHERE stock >= 4, or SELECT ... FOR UPDATE.
• Write skew (two doctors go off call) passes Repeatable Read. Serializable cancels one.

Watch out: Retry the whole transaction on 40001 (serialization) or 40P01 (deadlock). Limit tries, add jitter, no emails inside.

• ACCESS EXCLUSIVE (most ALTER TABLE) blocks even reads, and requests queue: one waiting ALTER froze a plain SELECT for 2.2 s. Use lock_timeout.
• Deadlock = opposite lock order. Lock the lower id first, keep transactions short. Detected after 1 s.
• Long or idle in transaction sessions block vacuum and bloat tables. Set idle_in_transaction_session_timeout.

-- job queue: workers never block
SELECT id FROM jobs
WHERE status = 'queued' ORDER BY id
LIMIT 1 FOR UPDATE SKIP LOCKED;
-- idempotency key in the same transaction
INSERT INTO payments (idem_key, ...)
ON CONFLICT (idem_key) DO NOTHING;

• GIN index on jsonb took a query from 173 ms to 16.7 ms. It serves @> and ?, not ->>. Planner has no stats per key.
• Fields you filter, join or constrain belong in real columns. Arrays: @> and && use GIN, = ANY(tags) does not.

INSERT INTO stock_levels (product_id, qty)
VALUES (1, 5)
ON CONFLICT (product_id) DO UPDATE
  SET qty = stock_levels.qty
          + EXCLUDED.qty
RETURNING *;

1. Start with SET lock_timeout = '500ms' and retry on failure.
2. NOT NULL: ADD CONSTRAINT ... CHECK (x IS NOT NULL) NOT VALID, VALIDATE, then SET NOT NULL.
3. Backfill in batches of about 100,000 rows, one transaction each.
4. CREATE INDEX CONCURRENTLY (not in a transaction). A failure leaves an INVALID index.
5. Rename or retype by expand and contract: add, sync, backfill, switch code, drop.

Tip: Test on production-sized data and read pg_locks. A 1,000-row dev table hides every problem here.