Tags: postgresql, sql, joins, indexes, window-functions, ctes, query-optimization, database Last updated: 2026-06-26

PostgreSQL / SQL Cheatsheet

Quick Reference

Concept	Example
INNER JOIN	`FROM a JOIN b ON a.id = b.a_id`
LEFT JOIN	`FROM a LEFT JOIN b ON a.id = b.a_id`
Index	`CREATE INDEX ON t (col)`
Window function	`ROW_NUMBER() OVER (PARTITION BY x ORDER BY y)`
CTE	`WITH cte AS (SELECT ...) SELECT ... FROM cte`
EXPLAIN	`EXPLAIN ANALYZE SELECT ...`
Upsert	`INSERT ... ON CONFLICT (id) DO UPDATE SET ...`
JSON	`data->'key'`, `data->>'key'`

Joins

-- Inner join (matching rows only)
SELECT u.name, o.total FROM users u
JOIN orders o ON u.id = o.user_id;

-- Left join (all users, even without orders)
SELECT u.name, o.total FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Lateral join (correlated subquery)
SELECT u.name, r.latest_order FROM users u
LEFT JOIN LATERAL (
  SELECT total AS latest_order FROM orders
  WHERE user_id = u.id
  ORDER BY created_at DESC LIMIT 1
) r ON true;

Indexes

-- B-tree
CREATE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX idx_email_uq ON users (email);

-- Composite
CREATE INDEX idx_orders_ud ON orders
  (user_id, created_at DESC);

-- Partial
CREATE INDEX idx_orders_active ON orders
  (created_at) WHERE status = 'active';

-- Expression
CREATE INDEX idx_lower_email ON users
  (LOWER(email));

-- GIN (full-text, arrays, JSON)
CREATE INDEX idx_body ON posts
  USING gin (to_tsvector('english', body));

-- BRIN (huge tables)
CREATE INDEX idx_events_time ON events
  USING brin (created_at);

-- Reindex without locking
REINDEX INDEX CONCURRENTLY idx_name;

Window Functions

-- Row number per group
SELECT name, dept, salary,
  ROW_NUMBER() OVER (PARTITION BY dept
    ORDER BY salary DESC)
FROM employees;

-- Running total
SUM(sales) OVER (ORDER BY date
  ROWS BETWEEN UNBOUNDED PRECEDING
  AND CURRENT ROW)

-- Moving average (last 7 days)
AVG(sales) OVER (ORDER BY date
  ROWS BETWEEN 6 PRECEDING
  AND CURRENT ROW)

-- Lead / Lag
LAG(sales, 1) OVER (ORDER BY date)
LEAD(sales, 1) OVER (ORDER BY date)

CTEs

WITH active_users AS (
  SELECT id, name FROM users
  WHERE status = 'active'
)
SELECT * FROM active_users;

-- Recursive CTE (hierarchy)
WITH RECURSIVE tree AS (
  SELECT id, name, manager_id, 1 AS lvl
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, t.lvl+1
  FROM employees e
  JOIN tree t ON e.manager_id = t.id
)
SELECT * FROM tree ORDER BY lvl, id;

Query Optimisation

EXPLAIN SELECT * FROM users
  WHERE email = '[email protected]';

EXPLAIN ANALYZE SELECT ...;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Upsert

INSERT INTO users (id, name, email)
VALUES (1, 'Max', '[email protected]')
ON CONFLICT (email) DO UPDATE
  SET name = EXCLUDED.name,
      updated_at = NOW();

Tips

Use EXPLAIN ANALYZE before deploying any query with joins to production.
Prefer EXISTS over IN for large subquery results.
Use CONCURRENTLY when creating/dropping indexes in production.