Recursive CTEs

A recursive CTE is a Common Table Expression that refers to itself, allowing a query to repeatedly build on its own previous results. It's the standard SQL tool for traversing hierarchical or graph-shaped data — org charts, category trees, bill-of-materials explosions, or any "parent points to child" structure of unknown depth.

Syntax: anchor + recursive member

A recursive CTE is written with WITH RECURSIVE and has two parts joined by UNION ALL:

Anchor member — a normal SELECT that produces the starting rows (the base case).
Recursive member — a SELECT that references the CTE's own name, joining it back to the source table to find the "next level."

Skeleton

SQL

WITH RECURSIVE cte_name AS (
  -- anchor member (base case)
  SELECT ...
  UNION ALL
  -- recursive member (references cte_name)
  SELECT ...
  FROM cte_name
  JOIN some_table ON ...
)
SELECT * FROM cte_name;

PostgreSQL repeatedly evaluates the recursive member, feeding it the rows produced in the previous iteration, until an iteration produces no new rows at all — at which point it stops and returns the accumulated result.

Worked example: an org chart

employees

SQL

id | name    | manager_id
---+---------+-----------
 1 | Alice   |      NULL
 2 | Bob     |         1
 3 | Carol   |         1
 4 | Dave    |         2
 5 | Erin    |         2

Find everyone who reports (directly or indirectly) to Alice

SQL

WITH RECURSIVE org_chart AS (
  -- Anchor: Alice herself, at depth 0
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE name = 'Alice'

  UNION ALL

  -- Recursive: each employee whose manager is already in org_chart
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY depth, name;

id | name  | manager_id | depth
---+-------+------------+------
 1 | Alice |       NULL |     0
 2 | Bob   |          1 |     1
 3 | Carol |          1 |     1
 4 | Dave  |          2 |     2
 5 | Erin  |          2 |     2

The first iteration finds Alice (depth 0). The second iteration joins employees against that single row and finds Bob and Carol (depth 1), since their manager_id matches Alice's id. The third iteration joins against Bob and Carol and finds Dave and Erin (depth 2). The fourth iteration finds no new matches, so recursion stops.

Another classic use case: bill of materials

The same shape solves "what parts make up this parts" — a product built from sub-assemblies, which are themselves built from smaller parts, to arbitrary depth.

parts (part_id, parent_part_id, name, quantity)

SQL

WITH RECURSIVE bom AS (
  SELECT part_id, parent_part_id, name, quantity, 1 AS multiplier
  FROM parts
  WHERE part_id = 100  -- the top-level product

  UNION ALL

  SELECT p.part_id, p.parent_part_id, p.name,
         p.quantity, bom.multiplier * p.quantity
  FROM parts p
  JOIN bom ON p.parent_part_id = bom.part_id
)
SELECT * FROM bom;

Always ensure a real termination condition

A recursive member must eventually stop producing new rows, or the query will run forever, consuming resources until you cancel it or the server runs out of memory. This usually goes wrong when the join condition in the recursive member is written loosely enough to keep matching the same rows (or an ever-growing set of rows) indefinitely — for example, joining on a condition that doesn't actually tie back to a strictly "deeper" row, or forgetting a WHERE clause that should stop expansion. If your source data can contain cycles (e.g. employee A manages B who manages A), also guard against infinite loops by tracking visited rows.

Guarding against cycles / runaway depth

SQL

WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 0 AS depth, ARRAY[id] AS path
  FROM employees
  WHERE name = 'Alice'

  UNION ALL

  SELECT e.id, e.name, e.manager_id, oc.depth + 1, oc.path || e.id
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
  WHERE e.id <> ALL(oc.path)   -- never revisit a row already in this path
    AND oc.depth < 50          -- hard safety cap
)
SELECT * FROM org_chart;

Note

UNION ALL (not UNION) is used deliberately in a recursive CTE — deduplicating with UNION would require comparing every accumulated row against every new row on each iteration, which is both usually unnecessary and considerably slower.

Tip

Recursive CTEs are also the standard way to generate a sequence procedurally (counting, date ranges before GENERATE_SERIES existed, running Fibonacci-style calculations) — the anchor sets the starting value and the recursive member describes how to compute the next one.

WITH RECURSIVE name AS (anchor UNION ALL recursive) builds a query that references its own result.
The anchor member is the base case; the recursive member joins back to the CTE to find the next level.
Recursion stops automatically once an iteration produces zero new rows.
Always ensure a real termination condition — guard against cycles in the source data to avoid infinite recursion.