Recursive CTE

Common Table Expression that references itself, used to walk hierarchies, graphs, and generated sequences.

A WITH RECURSIVE CTE has two parts: a non-recursive anchor query that produces the seed rows, and a recursive query that references the CTE name to extend the result. PostgreSQL repeatedly executes the recursive part against the rows produced in the previous iteration until no new rows appear.

WITH RECURSIVE org AS (
  SELECT id, manager_id, name FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, e.name
    FROM employees e JOIN org ON e.manager_id = org.id
)
SELECT * FROM org;

The same machinery handles graph traversal, generating numeric sequences, and parsing tree-shaped JSON. Cycle detection requires explicit work — track visited keys in an array column or use the CYCLE clause (PostgreSQL 14+) to terminate cleanly on revisits.

Related terms