CTEs and recursive queries

conceptThe concept

A CTE (WITH name AS (...)) is a named subquery visible to the rest of the statement. Before 12 every CTE was materialised: computed once and stored, which blocked predicate pushdown. Since 12, a side-effect-free CTE referenced once is inlined like a subquery; add MATERIALIZED to force the old behaviour or NOT MATERIALIZED to force inlining.

A recursive CTE has a non-recursive term (the anchor), UNION ALL, and a recursive term that references the CTE itself. PostgreSQL evaluates the anchor, then repeatedly runs the recursive term against the rows produced in the previous step until no new rows appear.

Data-modifying statements can live in WITH: WITH moved AS (DELETE ... RETURNING *) INSERT INTO archive SELECT * FROM moved archives rows atomically in one statement.

whyWhy it matters

Org charts, category trees, bill-of-materials and dependency graphs are recursive by nature. Doing the traversal in SQL is one round trip instead of N.

Use caseReal-world scenario

HR wants each employee with their full reporting chain and depth, and Finance wants the total salary under each VP. Both are a single recursive CTE over employees.

Try itExamples

Run and edit these in the interactive lesson.

WITH monthly AS (
  SELECT date_trunc('month', created_at) AS month, sum(total) AS revenue
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY 1
), ranked AS (
  SELECT month, revenue, rank() OVER (ORDER BY revenue DESC) AS rnk
  FROM monthly
)
SELECT to_char(month, 'YYYY-MM') AS month, revenue
FROM ranked
WHERE rnk <= 3
ORDER BY revenue DESC;

WITH RECURSIVE chain AS (
  SELECT id, name, title, manager_id, 0 AS depth, name::text AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.title, e.manager_id, c.depth + 1, c.path || ' > ' || e.name
  FROM employees e
  JOIN chain c ON e.manager_id = c.id
)
SELECT repeat('  ', depth) || name AS employee, title, depth, path
FROM chain
ORDER BY path;

WITH RECURSIVE tree AS (
  SELECT id AS root_id, id, salary FROM employees WHERE manager_id = 1
  UNION ALL
  SELECT t.root_id, e.id, e.salary
  FROM employees e JOIN tree t ON e.manager_id = t.id
)
SELECT r.name, r.title, count(*) AS people, sum(t.salary) AS total_salary
FROM tree t JOIN employees r ON r.id = t.root_id
GROUP BY r.name, r.title
ORDER BY total_salary DESC;

A temporary cycle makes the naive recursion loop forever. The CYCLE clause detects it and stops.

BEGIN;
UPDATE employees SET manager_id = 22 WHERE id = 19;

WITH RECURSIVE up AS (
  SELECT id, manager_id, name FROM employees WHERE id = 20
  UNION ALL
  SELECT e.id, e.manager_id, e.name FROM employees e JOIN up ON e.id = up.manager_id
) CYCLE id SET is_cycle USING path
SELECT name, is_cycle, path FROM up;
ROLLBACK;

BEGIN;
CREATE TEMP TABLE events_archive (LIKE events);
WITH moved AS (
  DELETE FROM events WHERE at < '2025-10-02' RETURNING *
)
INSERT INTO events_archive SELECT * FROM moved;
SELECT (SELECT count(*) FROM events_archive) AS archived,
       (SELECT min(at) FROM events) AS oldest_remaining;
ROLLBACK;

internalsUnder the hood

Recursion uses a working table and an intermediate table; with UNION (not ALL) PostgreSQL also discards duplicate rows, which stops some cycles but costs a hash of every row. The 14+ CYCLE clause tracks the path and marks the cycle explicitly, which is clearer and works with UNION ALL.

Avoid / preferDo this, not that

WITH RECURSIVE r AS (
  SELECT id, manager_id FROM employees WHERE id = 9
  UNION ALL
  SELECT e.id, e.manager_id FROM employees e JOIN r ON e.id = r.manager_id
)
SELECT * FROM r;  -- loops forever if the data has a cycle

WITH RECURSIVE r AS (
  SELECT id, manager_id FROM employees WHERE id = 9
  UNION ALL
  SELECT e.id, e.manager_id FROM employees e JOIN r ON e.id = r.manager_id
) CYCLE id SET is_cycle USING path
SELECT * FROM r WHERE NOT is_cycle;

WITH big AS (SELECT * FROM events)
SELECT * FROM big WHERE customer_id = 42;  -- assuming the CTE is computed first

WITH big AS NOT MATERIALIZED (SELECT * FROM events)
SELECT * FROM big WHERE customer_id = 42;

PitfallsCommon mistakes

• Adding MATERIALIZED out of habit from old versions, which prevents the planner from pushing filters into the CTE.
• Writing a recursive CTE without a termination guarantee on data that can contain cycles. Use the CYCLE clause, a depth limit, or UNION instead of UNION ALL.
• Expecting two data-modifying CTEs in the same statement to see each other's changes. All sub-statements see the same snapshot; pass rows along with RETURNING.

LimitsWhen not to use it

Recursive CTEs suit trees and small graphs. For deep traversals over millions of edges, a materialized path (ltree), a closure table or a graph engine is usually faster.

SecuritySecurity notes

A user-triggered recursive query without a depth limit is an easy way to burn CPU. Bound depth in the query and set statement_timeout for roles that serve user requests.

ChallengeTry it yourself

WITH RECURSIVE chain AS (
  SELECT id, manager_id, name::text AS path
  FROM employees WHERE name = 'Lena Vogel'
  UNION ALL
  SELECT e.id, e.manager_id, e.name || ' > ' || c.path
  FROM employees e JOIN chain c ON e.id = c.manager_id
)
SELECT path FROM chain WHERE manager_id IS NULL;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• WITH queries