A Query That Calls Itself: Recursive CTEs

WITH RECURSIVE, the one query that can refer to its own output — generating sequences of numbers and dates, walking a hierarchy row by row, and the cycle that runs forever until you stop it. Run against PostgreSQL 18.

Every query so far reads its inputs once. A recursive CTE is the exception: it feeds its own output back into itself, over and over, until a condition tells it to stop. That’s the one thing plain SQL can’t otherwise do. It’s exactly what you need for questions that are really loops in disguise: generate the twelve months of a year, walk a category tree from root to leaf, or follow a chain of references. If you’ve ever wanted to write a for loop in SQL, this is the closest the language comes, and it’s more useful than it sounds.

The shape: anchor, then recursion

A recursive CTE always has the same three-part skeleton, joined by UNION ALL:

  1. An anchor — a non-recursive query that produces the starting rows.
  2. The recursive term — a query that references the CTE’s own name, run again and again on the rows the previous round produced.
  3. A termination condition — usually a WHERE in the recursive term that eventually returns nothing, ending the loop.

The simplest possible example generates the numbers 1 through 10:

WITH RECURSIVE nums AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n, n * n AS squared FROM nums;
 n  | squared
----+---------
  1 |       1
  2 |       4
  3 |       9
  4 |      16
  5 |      25
  6 |      36
  7 |      49
  8 |      64
  9 |      81
 10 |     100
(10 rows)

Read the mechanism. The anchor SELECT 1 seeds the CTE with a single row, n = 1. The recursive term takes the rows produced last round — just {1} to start — and computes n + 1 for each where n < 10, giving {2}. That feeds back in, producing {3}, and so on. When n reaches 10, the WHERE n < 10 filters it out, the recursive term returns zero rows, and the loop stops. UNION ALL stacks every round’s output into the final result. The keyword is WITH RECURSIVE, and it’s required even though only one of the CTEs is actually recursive.

Postgres has generate_series(1, 10) built in and you’d use it for a plain integer range. The point here isn’t the numbers; it’s the pattern, because the same skeleton generates things generate_series can’t.

Generating a date spine

A common real need is a date spine: one row per month (or day) across a range. You left-join your data onto it and get zeros for the empty periods instead of missing rows. Recursion builds one directly:

WITH RECURSIVE months AS (
  SELECT DATE '2025-01-01' AS month
  UNION ALL
  SELECT (month + INTERVAL '1 month')::date
  FROM months
  WHERE month < DATE '2025-09-01'
)
SELECT month FROM months;
   month
------------
 2025-01-01
 2025-02-01
 2025-03-01
 2025-04-01
 2025-05-01
 2025-06-01
 2025-07-01
 2025-08-01
 2025-09-01
(9 rows)

Same skeleton. The anchor is the first month. The recursive term adds one month with + INTERVAL '1 month', cast back to date so it stays a clean date and not a timestamp, and stops once it passes September. Now every month exists as a row whether or not any orders landed in it — the fix for the classic “my chart is missing April because April had no sales” bug. (Postgres also has generate_series('2025-01-01'::date, '2025-09-01', '1 month') for exactly this, and you’d usually reach for it. Recursion is the portable fallback, and the thing to understand underneath.)

Walking a hierarchy

The reason recursive CTEs actually earn their place is hierarchies — data that points at itself. A category tree, an org chart, a threaded comment list, a bill of materials. Each row names its parent, and to render the tree you have to follow the chain, which is a loop of unknown depth.

The bookshop’s tables are flat, so here’s a category tree built inline with VALUES, each row carrying its own id and its parent’s id:

WITH RECURSIVE
categories(id, name, parent_id) AS (
  VALUES (1, 'All Books', NULL),
         (2, 'Fiction',    1),
         (3, 'Nonfiction', 1),
         (4, 'Sci-Fi',     2),
         (5, 'Fantasy',    2),
         (6, 'History',    3),
         (7, 'Space Opera',4)
),
tree AS (
  SELECT id, name, parent_id, 1 AS depth, name::text AS path
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1, t.path || ' > ' || c.name
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT depth, repeat('  ', depth - 1) || name AS label, path
FROM tree
ORDER BY path;
 depth |       label       |                    path
-------+-------------------+--------------------------------------------
     1 | All Books         | All Books
     2 |   Fiction         | All Books > Fiction
     3 |     Fantasy       | All Books > Fiction > Fantasy
     3 |     Sci-Fi        | All Books > Fiction > Sci-Fi
     4 |       Space Opera | All Books > Fiction > Sci-Fi > Space Opera
     2 |   Nonfiction      | All Books > Nonfiction
     3 |     History       | All Books > Nonfiction > History
(7 rows)

This is the pattern worth memorizing. The anchor selects the root — the row whose parent_id IS NULL — and starts depth at 1 with path set to its own name. The recursive term joins categories to the rows found so far on c.parent_id = t.id: it finds every category whose parent was reached last round. Each step carries two accumulators down the tree — depth + 1 for indentation, and path || ' > ' || name building a breadcrumb string. Ordering by that path and indenting by depth prints the tree in the shape you’d draw it. Swap the inline VALUES for a real self-referencing table and this same query renders any hierarchy you’ve got.

The loop that never ends

Recursion’s gift is also its footgun: if the recursive term never returns zero rows, the query runs forever. The number generator stopped because n < 10 eventually failed. Remove a stopping condition, or point the data at itself in a cycle, and there’s nothing to end the loop. Postgres has no default recursion depth limit — it will happily keep going until it exhausts memory or you cancel it.

Here’s a three-node cycle, 1 → 2 → 3 → 1, with a timeout set so the demonstration is safe:

SET statement_timeout = '800ms';
WITH RECURSIVE edges(a, b) AS (
  VALUES (1, 2), (2, 3), (3, 1)
),
walk AS (
  SELECT a, b, 1 AS steps FROM edges WHERE a = 1
  UNION ALL
  SELECT e.a, e.b, w.steps + 1
  FROM edges e JOIN walk w ON e.a = w.b
)
SELECT count(*) FROM walk;
ERROR:  canceling statement due to statement timeout

It never finishes. The walk goes 1→2, 2→3, 3→1, 1→2 again, forever, because the graph loops back on itself and nothing prunes the revisit. The timeout is the only thing that saved us — in production, an unbounded recursive CTE is a way to take a database down.

The fix built into SQL is the CYCLE clause (Postgres 14 and up). You name the column that identifies a node, a flag column to mark when a repeat is seen, and a path column it uses to track where it’s been:

WITH RECURSIVE
edges(a, b) AS (
  VALUES (1, 2), (2, 3), (3, 1)
),
walk AS (
  SELECT a, b, 1 AS steps FROM edges WHERE a = 1
  UNION ALL
  SELECT e.a, e.b, w.steps + 1
  FROM edges e JOIN walk w ON e.a = w.b
) CYCLE a SET is_cycle USING cyclepath
SELECT a, b, steps, is_cycle FROM walk;
 a | b | steps | is_cycle
---+---+-------+----------
 1 | 2 |     1 | f
 2 | 3 |     2 | f
 3 | 1 |     3 | f
 1 | 2 |     4 | t
(4 rows)

The moment the walk revisits node 1, CYCLE sets is_cycle to true and refuses to recurse further, so the query terminates. Even without CYCLE, a defensive WHERE steps < 100 in the recursive term is a cheap seatbelt on any real hierarchy walk. It caps the depth, so a bad row can’t spin forever. MySQL 8 supports WITH RECURSIVE and enforces a cte_max_recursion_depth limit (1,000 by default) instead of running unbounded; SQLite supports it with no default limit, like Postgres.

Final thoughts

A recursive CTE is a loop written as a query: an anchor to start, a recursive term that reads its own output, and a condition that ends it. It generates sequences that plain SELECT can’t and, more importantly, walks any self-referencing hierarchy — trees, chains, graphs — from root to leaf in one statement. The discipline is the termination. Every recursive CTE needs a reason to stop, and when the data can loop back on itself you need CYCLE or a depth cap, because Postgres will not save you by default. Master the three-part skeleton and a whole class of “but that’s really a loop” problems collapses into a single query. Next we meet the other tool that computes across many rows without collapsing them — the window function, which will run the rest of this series.

Next: Window functions: seeing a row and its neighbours

Comments