Recursive CTE

A recursive CTE uses the WITH RECURSIVE syntax to define a query that references itself. It is the standard SQL approach for traversing hierarchical and nested data structures — such as org charts, category trees, and graphs — without requiring procedural code or multiple round trips to the database.

Recursive CTE syntax

WITH RECURSIVE cte_name (column1, column2, ...) AS (
  -- Anchor member: runs once, produces the base result set
  SELECT select_list FROM table1
  WHERE condition

  UNION [ALL]

  -- Recursive member: references cte_name, runs until empty
  SELECT select_list FROM table1
  INNER JOIN cte_name ON cte_name.id = table1.parent_id
  WHERE recursive_condition
)
SELECT * FROM cte_name;

PostgreSQL executes the recursive CTE as follows:

1. Run the anchor member once to produce the initial result set R0.
2. Run the recursive member using the previous result as input, producing R1.
3. Repeat step 2 until the recursive member returns an empty result set.
4. Return the final result: the UNION (or UNION ALL) of R0, R1, R2, ...

Example: traversing an org chart

Given an employees table with employee_id, full_name, and manager_id:

WITH RECURSIVE subordinates AS (
  -- Anchor: start with employee 2 (Megan Berry)
  SELECT employee_id, manager_id, full_name
  FROM employees
  WHERE employee_id = 2

  UNION

  -- Recursive: find all employees who report to someone in subordinates
  SELECT e.employee_id, e.manager_id, e.full_name
  FROM employees e
  INNER JOIN subordinates s ON s.employee_id = e.manager_id
)
SELECT * FROM subordinates;

Output:

 employee_id | manager_id |    full_name
-------------+------------+-----------------
           2 |          1 | Megan Berry
           6 |          2 | Bella Tucker
           7 |          2 | Ryan Metcalfe
           8 |          2 | Max Mills
           9 |          2 | Benjamin Glover
          16 |          7 | Piers Paige
          17 |          7 | Ryan Henderson
          18 |          8 | Frank Tucker
          19 |          8 | Nathan Ferguson
          20 |          8 | Kevin Rampling
(10 rows)

The anchor returns just Megan Berry (ID 2). The first recursive iteration finds her 4 direct reports. The second finds their subordinates. The third returns empty (no one reports to IDs 16–20), terminating the recursion.

Tracking depth and building paths

Add a depth counter and a path array to enrich the output and guard against cycles:

WITH RECURSIVE org_tree AS (
  SELECT
    employee_id,
    full_name,
    manager_id,
    1 AS depth,
    ARRAY[employee_id] AS path
  FROM employees
  WHERE manager_id IS NULL  -- root: no manager

  UNION ALL

  SELECT
    e.employee_id,
    e.full_name,
    e.manager_id,
    t.depth + 1,
    t.path || e.employee_id
  FROM employees e
  INNER JOIN org_tree t ON t.employee_id = e.manager_id
  WHERE NOT e.employee_id = ANY(t.path)  -- cycle guard
)
SELECT depth, full_name, path
FROM org_tree
ORDER BY path;

Key tips for recursive CTEs

• Use UNION ALL for tree data (no cycles) — it is faster than UNION because it skips deduplication. Use UNION or an explicit cycle-detection array for graph data where cycles are possible.
• Always ensure the recursive member has a termination condition. Add a depth limit (WHERE depth < 100) or a path-based cycle check as a safety measure on unfamiliar data.
• The anchor and recursive member must return the same number of columns with compatible types — just like UNION.
• Recursive CTEs are always materialized in PostgreSQL (they cannot be inlined), so each iteration executes as a separate step. For very deep hierarchies or wide tables, select only the columns you need.
• To traverse from a leaf node upward to the root, swap the join direction: join the CTE on the manager_id side instead of the employee_id side in the recursive member.

Reference: PostgreSQL documentation — WITH Queries (Common Table Expressions).