WITH RECURSIVE

A CTE that refers to itself to walk hierarchies or generate sequences.

Syntax

WITH RECURSIVE tree AS (
  SELECT ...                     -- anchor: the starting rows
  UNION ALL
  SELECT ... FROM source s
  JOIN tree t ON s.parent_id = t.id   -- recursive step: next level
)
SELECT ... FROM tree;

PostgreSQL runs the anchor query once, then repeatedly runs the recursive part against the rows produced in the previous round, until a round produces no rows. The result is all rounds combined.

Carry a depth column (0 in the anchor, t.depth + 1 in the step) and a path (text or array) to show levels and to sort the tree.
Use UNION ALL normally; UNION removes duplicates and can stop cycles that revisit identical rows.
Cycles (A → B → A) loop forever. Guard with a depth limit (WHERE t.depth < 20), a path check (NOT s.id = ANY(t.path)), or the SQL-standard CYCLE id SET is_cycle USING path clause.
Column types must match between anchor and step; cast text literals in the anchor (name::text).

Examples

Category paths shop

WITH RECURSIVE tree AS (
  SELECT category_id, category_name, 0 AS depth, category_name::text AS path
  FROM categories
  WHERE parent_category_id IS NULL
  UNION ALL
  SELECT c.category_id, c.category_name, t.depth + 1, t.path || ' > ' || c.category_name
  FROM categories c
  JOIN tree t ON c.parent_category_id = t.category_id
)
SELECT category_id, depth, path
FROM tree
ORDER BY path;

Departments at depth 0, their children at depth 1, and so on, with a readable path.

Everyone under one manager people

WITH RECURSIVE reports AS (
  SELECT employee_id, manager_id, 1 AS level
  FROM employees
  WHERE manager_id = (SELECT employee_id FROM employees WHERE manager_id IS NULL ORDER BY employee_id LIMIT 1)
  UNION ALL
  SELECT e.employee_id, e.manager_id, r.level + 1
  FROM employees e
  JOIN reports r ON e.manager_id = r.employee_id
)
SELECT level, count(*) AS employees
FROM reports
GROUP BY level
ORDER BY level;

Head count at each level below the top of the hierarchy.

Generate a sequence starter

WITH RECURSIVE n AS (
  SELECT 1 AS i
  UNION ALL
  SELECT i + 1 FROM n WHERE i < 10
)
SELECT i FROM n;

The WHERE in the recursive step is what stops the recursion (generate_series is simpler in PostgreSQL).

Pitfalls

No stopping condition or a cycle in the data: the query runs until it times out.
Type mismatch between anchor and recursive part (e.g. varchar vs text, integer vs bigint).
Using the recursive CTE name more than once in the recursive step, or inside an aggregate (not allowed).

Other databases

For orientation only: SQL Quest executes PostgreSQL.

SQL Server: Write WITH (no RECURSIVE keyword); default MAXRECURSION is 100.
Oracle: Write WITH (no RECURSIVE keyword) and list the columns: WITH t (a, b) AS (...); CONNECT BY is the older syntax.
MySQL: WITH RECURSIVE since 8.0; cte_max_recursion_depth defaults to 1000.