Recursive Queries in PostgreSQL

Recursive CTEs let you traverse hierarchical or graph-like data stored in a single table. They work by defining a base case and a recursive step that references itself until no more rows are produced.

Structure of a Recursive CTE

WITH RECURSIVE cte_name AS (
  -- Base case: starting rows
  SELECT ...
  UNION ALL
  -- Recursive step: references cte_name
  SELECT ...
  FROM cte_name
  JOIN ...
)
SELECT * FROM cte_name;

The base case runs once to seed the result. The recursive step then joins back to the CTE's own output, running repeatedly until it produces no new rows.

Example: Employee Org Chart

-- Table: employees (id, name, manager_id)

WITH RECURSIVE org_chart AS (
  -- Base case: the CEO (no manager)
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive step: find direct reports
  SELECT e.id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT
  REPEAT('  ', depth - 1) || name AS org_tree,
  depth
FROM org_chart
ORDER BY depth, name;

This starts with the CEO (where manager_id IS NULL), then finds all employees who report to the CEO, then their reports, and so on. The depth column tracks how many levels deep each person is.

Example: Category Tree

-- Table: categories (id, name, parent_id)

-- Get all subcategories under "Electronics" (id = 1)
WITH RECURSIVE subcategories AS (
  SELECT id, name, parent_id, ARRAY[name] AS path
  FROM categories
  WHERE id = 1

  UNION ALL

  SELECT c.id, c.name, c.parent_id, sc.path || c.name
  FROM categories c
  JOIN subcategories sc ON c.parent_id = sc.id
)
SELECT
  id,
  name,
  ARRAY_TO_STRING(path, ' > ') AS breadcrumb
FROM subcategories;

The path array accumulates the names along the traversal, producing a breadcrumb like "Electronics > Computers > Laptops".

Preventing Infinite Loops

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, ARRAY[id] AS visited
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, t.visited || c.id
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
  WHERE c.id <> ALL(t.visited)  -- prevent cycles
)
SELECT * FROM tree;

Common Use Cases

• Organizational hierarchies (managers and reports)
• Nested category or menu trees
• Bill of materials (parts and sub-parts)
• Graph traversal such as shortest path or reachability