Skip to content
GreenSnippets

Walk a tree with a recursive CTE

Query an employee/manager or category hierarchy to any depth using WITH RECURSIVE.

SQL
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 0 AS depth, ARRAY[id] AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, o.depth + 1, o.path || e.id
  FROM employees AS e
  JOIN org AS o ON e.manager_id = o.id
  WHERE NOT e.id = ANY (o.path)
)
SELECT repeat('  ', depth) || name AS tree, depth
FROM org
ORDER BY path;

Example / expected output

Output
-- Ada
--   Lin
--     Bo
--   Sam

How it works

A recursive CTE has an anchor query (the root rows) and a recursive part that joins the CTE to the table. Track depth and a path array to order results and to detect cycles; the NOT ... = ANY(path) check stops infinite loops if bad data creates a cycle.

This snippet has been reviewed but was not executed by an automated test. Test it in your own environment before relying on it.

Related snippets