Find Hierarchy Using Recursive CTE in PostgreSQL: Simple Guide

Use a WITH RECURSIVE common table expression (CTE) to traverse hierarchical data in PostgreSQL. Start with a base query selecting root nodes, then recursively join to find child nodes until the full hierarchy is built.

Syntax

The recursive CTE syntax has two parts: the anchor member and the recursive member.

• Anchor member: Selects the root or starting rows.
• Recursive member: Joins the CTE to the base table to find child rows.
• Final SELECT: Queries the CTE to get the full hierarchy.

sql

WITH RECURSIVE cte_name AS (
  -- Anchor member: select root rows
  SELECT id, parent_id, name
  FROM table_name
  WHERE parent_id IS NULL
  UNION ALL
  -- Recursive member: select children
  SELECT t.id, t.parent_id, t.name
  FROM table_name t
  JOIN cte_name c ON t.parent_id = c.id
)
SELECT * FROM cte_name;

Example

This example shows how to find all employees in a company hierarchy starting from the CEO (who has no manager).

sql

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  manager_id INT REFERENCES employees(id)
);

INSERT INTO employees (name, manager_id) VALUES
('Alice', NULL),
('Bob', 1),
('Carol', 1),
('Dave', 2),
('Eve', 2),
('Frank', 3);

WITH RECURSIVE employee_hierarchy AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, eh.level + 1
  FROM employees e
  JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy ORDER BY level, id;

Output

 id | name  | manager_id | level 
----+-------+------------+-------
  1 | Alice |            |     1
  2 | Bob   |          1 |     2
  3 | Carol |          1 |     2
  4 | Dave  |          2 |     3
  5 | Eve   |          2 |     3
  6 | Frank |          3 |     3

Common Pitfalls

Common mistakes when using recursive CTEs for hierarchy include:

• Not specifying a proper anchor member, causing no starting point.
• Missing the UNION ALL keyword between anchor and recursive parts.
• Incorrect join condition in the recursive member, which breaks the chain.
• Infinite recursion if cycles exist in data (e.g., a node points to itself).

Always test with sample data and consider adding a MAXRECURSION-like limit using LIMIT or a depth counter.

sql

/* Wrong: missing UNION ALL */
WITH RECURSIVE cte AS (
  SELECT id FROM table_name WHERE parent_id IS NULL
  SELECT id FROM table_name t JOIN cte c ON t.parent_id = c.id
)
SELECT * FROM cte;

/* Correct: include UNION ALL */
WITH RECURSIVE cte AS (
  SELECT id FROM table_name WHERE parent_id IS NULL
  UNION ALL
  SELECT t.id FROM table_name t JOIN cte c ON t.parent_id = c.id
)
SELECT * FROM cte;

Quick Reference

Tips for using recursive CTEs in PostgreSQL:

• Use WITH RECURSIVE to define recursive queries.
• Start with an anchor query selecting root nodes.
• Use UNION ALL to combine anchor and recursive parts.
• Join recursive part on parent-child relationship.
• Include a level or depth column to track hierarchy depth.
• Watch out for cycles to avoid infinite loops.

Key Takeaways

Use WITH RECURSIVE to query hierarchical data in PostgreSQL.

Anchor member selects root nodes; recursive member finds children.

Always include UNION ALL between anchor and recursive parts.

Track hierarchy depth with a level column for clarity.

Beware of cycles to prevent infinite recursion.