Recursive CTEs

A regular CTE (WITH) gives a query a name and runs it once. A recursive CTE calls itself — it starts with a base case, then repeats a recursive step, accumulating rows until no new rows appear. This is how SQL handles trees, hierarchies, and graphs without application-level loops.

The seed has two tables: an org_chart (a classic manager→employee tree) and edges (a small directed graph for path-finding).

SELECT * FROM org_chart ORDER BY id;

The shape of WITH RECURSIVE

Every recursive CTE has the same skeleton:

WITH RECURSIVE cte_name AS (
  -- 1. Base case: the starting rows
  SELECT …
  UNION ALL
  -- 2. Recursive step: joins back to itself
  SELECT … FROM … JOIN cte_name ON …
)
SELECT * FROM cte_name;

Postgres executes it iteratively: run the base case, then run the recursive step using only the new rows from the previous iteration, appending results each time. It stops when the recursive step produces zero new rows.

Walking down: "Who reports to Bob?"

Start at Bob (id 2), then repeatedly find everyone whose manager_id matches anyone already found:

WITH RECURSIVE reports AS (
-- Base: Bob himself
SELECT id, name, title, manager_id, 0 AS depth
FROM org_chart
WHERE id = 2
UNION ALL
-- Recursive: anyone whose manager is already in 'reports'
SELECT e.id, e.name, e.title, e.manager_id, r.depth + 1
FROM org_chart e
JOIN reports r ON e.manager_id = r.id
)
SELECT name, title, depth
FROM reports
ORDER BY depth, name;

Bob at depth 0, his direct reports at depth 1, their reports at depth 2, and so on. Each iteration adds one level of the tree.

Walking up: "Who is Eve's chain of command?"

Flip direction — start at Eve and follow manager_id upward:

WITH RECURSIVE chain AS (
SELECT id, name, manager_id, 1 AS level
FROM org_chart
WHERE name = 'Eve'
UNION ALL
SELECT o.id, o.name, o.manager_id, c.level + 1
FROM org_chart o
JOIN chain c ON o.id = c.manager_id
)
SELECT name, level
FROM chain
ORDER BY level;

Eve → Bob → Alice. Three levels of management.

Building a path string

Often you want the full path as a readable string. Concatenate as you recurse: