TopolearnHierarchies (categories, org charts, threaded comments) cannot be traversed to arbitrary depth with plain joins.
Stored hierarchies usually look like categories(id, parent_id, name): each row points at its parent. A join finds one level; finding all descendants, at any depth, needs recursion.
A recursive CTE has two parts joined by UNION ALL:
1. The anchor (non-recursive term) produces the starting rows. 2. The recursive term refers to the CTE itself and produces the next level from the rows of the previous step.
PostgreSQL evaluates the anchor, then repeatedly runs the recursive term against only the rows produced in the previous iteration, until an iteration produces nothing. The result is everything accumulated.
WITH RECURSIVE tree AS (
SELECT id, name, 1 AS depth FROM categories WHERE id = 2 -- anchor
UNION ALL
SELECT c.id, c.name, t.depth + 1
FROM categories c JOIN tree t ON c.parent_id = t.id -- step
)
SELECT * FROM tree;Carrying extra columns along is the main trick: a depth counter, or a path built by appending to an array or string, which also sorts the output in tree order.
The danger is cycles. If data ever loops (A's parent is B, B's parent is A), UNION ALL recursion never ends. Defences: use UNION instead (stops when no new distinct rows appear, but only works if rows repeat exactly), keep a path array and stop when the id is already in it, or use PostgreSQL 14's standard CYCLE id SET is_cycle USING path clause. A depth limit in the recursive term (WHERE t.depth < 20) is a cheap safety net.
Recursive CTEs also generate sequences. Dialects: the standard and PostgreSQL, MySQL 8 and SQLite need the RECURSIVE keyword; SQL Server and Oracle omit it, and older Oracle code uses CONNECT BY.
Example
-- categories: 1 All (parent NULL), 2 Hardware (1), 3 Input devices (2),
-- 4 Displays (2), 5 Digital (1), 6 Keyboards (3)
WITH RECURSIVE tree AS (
SELECT id, name, 1 AS depth, ARRAY[name] AS path
FROM categories
WHERE id = 2 -- start at Hardware
UNION ALL
SELECT c.id, c.name, t.depth + 1, t.path || c.name
FROM categories c
JOIN tree t ON c.parent_id = t.id
WHERE t.depth < 20 -- safety net
)
SELECT repeat(' ', depth - 1) || name AS category, depth
FROM tree
ORDER BY path;
-- category | depth
-- Hardware | 1
-- Displays | 2
-- Input devices | 2
-- Keyboards | 3Remember
- A recursive CTE is an anchor UNION ALL a step that references the CTE
- Each iteration sees only the previous iteration's new rows
- Carry depth and path columns to label and sort the tree
- Guard against cycles: CYCLE clause, path checks or a depth limit
Watch out for
- Recursing over data with a cycle using UNION ALL and no guard, producing a query that never finishes.