Recursive CTEs in PostgreSQL: Hierarchies & Sequences

When you need to walk a tree of unknown depth — org charts, category trees, bill-of-materials — a recursive CTE is the tool. Learn the anchor/recursive structure and how to stay safe.

A self join follows one level of a hierarchy. But org charts, category trees, and reporting chains are arbitrarily deep — you do not know in advance how many levels there are. A recursive CTE handles exactly this: it repeatedly applies a query to its own output until there is nothing left to add. Keep the SQL cheat sheet open as you read.

Why Recursion?

If a query needs to follow a chain of unknown length — every ancestor of a comment, every subordinate under a manager, every part inside an assembly — no fixed number of JOINs will do. Recursive CTEs express "keep following the links until they run out". They build on ordinary CTEs, so read subqueries vs CTEs first if WITH is new to you.

Anchor + Recursive Member

A recursive CTE is written as WITH RECURSIVE and has two parts joined by UNION ALL. The anchor member runs once and seeds the result. The recursive member then runs repeatedly, each time referencing the CTE itself, feeding on the rows the previous step produced — until a step produces no new rows.

WITH RECURSIVE cte AS (
  -- anchor: the starting rows (runs once)
  SELECT ...
  UNION ALL
  -- recursive member: references cte, runs until it adds nothing
  SELECT ...
  FROM some_table
  JOIN cte ON ...
)
SELECT * FROM cte;

How recursion builds up — The anchor seeds the result, then each iteration extends it with the next level — until a step produces no new rows and recursion stops.

Walking a Hierarchy

Here is the canonical example: everyone in the reporting chain under a given manager. The anchor selects the manager; the recursive member finds the direct reports of anyone already in the result, then their reports, and so on.

WITH RECURSIVE reports AS (
  -- anchor: the top manager
  SELECT id, name, manager_id
  FROM employees
  WHERE id = 1
  UNION ALL
  -- recursive: people who report to someone already found
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN reports r ON e.manager_id = r.id
)
SELECT * FROM reports;

Tracking Depth & Path

Carry an extra column to record how deep each row sits, and an array to record the full path from the root. This is what powers indented tree views and breadcrumb trails.

WITH RECURSIVE tree AS (
  SELECT id, name, manager_id,
         1 AS depth,
         ARRAY[name] AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id,
         t.depth + 1,
         t.path || e.name
  FROM employees e
  JOIN tree t ON e.manager_id = t.id
)
SELECT depth, name, path FROM tree
ORDER BY path;

Generating Sequences

Recursion is not only for trees. You can generate a series of numbers or dates — handy for filling gaps in time-series reports where some days have no rows.

-- Numbers 1 through 10
WITH RECURSIVE nums AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;

Avoiding Infinite Loops

If your data has a cycle (A reports to B, B reports to A) the recursion never produces zero rows and runs forever. Guard against it by tracking the visited path and stopping when you revisit a node, or by capping depth.

-- Stop if we would revisit a node (cycle guard)
WITH RECURSIVE tree AS (
  SELECT id, manager_id, ARRAY[id] AS visited
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, t.visited || e.id
  FROM employees e
  JOIN tree t ON e.manager_id = t.id
  WHERE e.id <> ALL(t.visited)   -- don't revisit
)
SELECT * FROM tree;

Common Mistakes & Tips

• Forgetting the RECURSIVE keyword — the CTE cannot reference itself without it
• Using UNION instead of UNION ALL without reason — UNION adds a de-dup pass each step
• No termination condition, or a cycle in the data, causing an infinite loop
• Putting heavy filtering after the CTE instead of inside the recursive member, so it scans more than needed
• Reaching for recursion when generate_series or a simple self join would do

Recursion is a genuine step up, and it impresses in interviews. Practice it on hierarchical data until the anchor/recursive split feels natural.