Common Table Expressions

Code

-- Simple CTE for readability
WITH spend AS (
  SELECT customer_id, SUM(total) AS total
  FROM orders
  WHERE status = 'completed'
  GROUP BY customer_id
)
SELECT c.name, spend.total
FROM customers c
JOIN spend ON spend.customer_id = c.id
WHERE spend.total > 1000;

-- Recursive CTE: org hierarchy
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e
  JOIN org o ON e.manager_id = o.id
)
SELECT id, name, depth FROM org ORDER BY depth;

Explanation

CTEs (WITH clauses) break complex queries into named stages for readability and reuse. Recursive CTEs reference themselves, enabling tree or graph traversal such as org charts or bill-of-materials. Use UNION ALL between the anchor and recursive parts to avoid duplicate elimination overhead.

More PostgreSQL Snippets