← Back to TIL

recursive ctes in postgresql

Feb 3, 2026

postgresqlsqlrecursion

what it is

postgresql’s recursive common table expressions (CTEs) let a query reference itself. it’s not a procedural loop, but it gives sql a way to express iteration declaratively.

how it works

a recursive cte has two parts:

  • base case — the initial rows
  • recursive term — a query that references the cte itself

they’re combined using union or union all. postgresql keeps running the recursive term, feeding it the previous result set, until no new rows are produced.

classic use cases

  • hierarchical data — org charts, category trees, file systems
  • graph traversal — walking edges, finding reachable nodes, computing depth
  • sequences / series — numbers, dates, or other iterative structures

anything where “next rows depend on previous rows”.

example pattern

with recursive tree as (
  -- base case
  select id, parent_id, name
  from categories
  where parent_id is null

  union all

  -- recursive term
  select c.id, c.parent_id, c.name
  from categories c
  join tree t on c.parent_id = t.id
)
select * from tree;

once you know this exists, a lot of app-side loops and ugly workarounds start feeling unnecessary.

variations