PostgreSQL CTEs and recursive queries

A Common Table Expression (WITH name AS (...)) gives a subquery a name so the main query can use it like a table. It turns deeply nested SQL into readable steps. You can chain several CTEs, and later ones can use earlier ones.

WITH RECURSIVE lets a CTE refer to itself. It has an anchor part (the starting rows) and a recursive part joined by UNION ALL, which runs repeatedly on the previous step’s rows until it returns nothing. Use it for hierarchies (org charts, category trees, threaded comments) and graph walks.

Syntax

WITH step1 AS (SELECT ...),
     step2 AS (SELECT ... FROM step1)
SELECT ... FROM step2;

WITH RECURSIVE r AS (
  SELECT ...            -- anchor
  UNION ALL
  SELECT ... FROM r ... -- recursive step
)
SELECT * FROM r;

Examples

Edit the code and run it. Output is printed the way psql prints it.

13.1Readable steps

WITH order_totals AS (
  SELECT o.id, o.customer_id, sum(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY o.id
),
customer_totals AS (
  SELECT customer_id, count(*) AS orders, sum(total) AS spend
  FROM order_totals
  GROUP BY customer_id
)
SELECT c.name, ct.orders, ct.spend
FROM customer_totals ct
JOIN customers c ON c.id = ct.customer_id
ORDER BY ct.spend DESC;

Step one computes each order’s total; step two rolls those up per customer; the final query adds names. Each piece can be tested on its own.

13.2Recursive: counting to 10

WITH RECURSIVE numbers AS (
  SELECT 1 AS n          -- anchor: start at 1
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 10   -- step
)
SELECT n, n * n AS square FROM numbers;

The anchor produces 1. The step takes the previous row and produces n+1, and stops when the WHERE fails. Always make sure recursion has a stopping condition.

13.3Recursive: the org chart

WITH RECURSIVE org AS (
  SELECT id, first_name, manager_id, 1 AS level,
         first_name::text AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.first_name, e.manager_id, org.level + 1,
         org.path || ' > ' || e.first_name
  FROM employees e
  JOIN org ON e.manager_id = org.id
)
SELECT repeat('    ', level - 1) || first_name AS tree, level, path
FROM org
ORDER BY path;

Anchor: the top bosses (no manager).
Step: everyone whose manager is in the previous level.
path builds the chain of names; sorting by it prints a tree.

Exercises

Write a query and check it. Any query that returns the right rows passes.

Exercise 13.1

Using a CTE, list product categories whose total revenue (quantity × unit_price over all order items) is above 1500. Columns: category, revenue.

Hint

CTE joining order_items to products grouped by category.

Solution

WITH rev AS (SELECT p.category, sum(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN products p ON p.id = oi.product_id GROUP BY p.category) SELECT category, revenue FROM rev WHERE revenue > 1500;

Exercise 13.2

With a recursive CTE, list the management chain above Rohan (employee id 8): id and first_name of each manager up to the top.

Hint

Anchor: SELECT manager_id of employee 8. Step: join employees on id = previous manager_id.

Solution

WITH RECURSIVE chain AS (SELECT e.id, e.first_name, e.manager_id FROM employees e WHERE e.id = (SELECT manager_id FROM employees WHERE id = 8) UNION ALL SELECT e.id, e.first_name, e.manager_id FROM employees e JOIN chain c ON e.id = c.manager_id) SELECT id, first_name FROM chain;