The essentials

Quick reference

One focused task per row. Jump to the related section for complete, working examples.

UseSyntaxExamples
Generate a bounded sequenceWITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n + 1 FROM t WHERE n < 10 ) SELECT n FROM t;View examples
Walk childrenWITH RECURSIVE t(id) AS ( VALUES (42::bigint) UNION ALL SELECT e.to_id FROM app.edges e JOIN t ON e.from_id=t.id ) SELECT * FROM t;View examples
Remove duplicate rowsWITH RECURSIVE t(id) AS ( VALUES (1::bigint) UNION SELECT to_id FROM app.edges JOIN t ON from_id=t.id ) SELECT id FROM t;View examples
Cap traversal depthWITH RECURSIVE t(n,d) AS ( VALUES (1,0) UNION ALL SELECT n+1,d+1 FROM t WHERE d<20 ) SELECT * FROM t;View examples
Set a statement budgetSET LOCAL statement_timeout = '10s';View examples
Append to a pathSELECT ARRAY[1,2] || 3;View examples
Test path membershipSELECT 3 = ANY (ARRAY[1,2,3]);View examples
Declare cycle detectionWITH RECURSIVE t(id) AS ( VALUES (1::bigint) UNION ALL SELECT to_id FROM app.edges JOIN t ON from_id=t.id ) CYCLE id SET cyc USING path SELECT * FROM t;View examples
Filter cycle-closing rowsSELECT id, path FROM walk WHERE NOT is_cycle;View examples
Create depth-first orderWITH RECURSIVE t(id) AS ( VALUES (1::bigint) UNION ALL SELECT to_id FROM app.edges JOIN t ON from_id=t.id ) SEARCH DEPTH FIRST BY id SET ord SELECT * FROM t;View examples
Create breadth-first orderWITH RECURSIVE t(id) AS ( VALUES (1::bigint) UNION ALL SELECT to_id FROM app.edges JOIN t ON from_id=t.id ) SEARCH BREADTH FIRST BY id SET ord SELECT * FROM t;View examples
Order final outputSELECT id FROM tree ORDER BY bfs_order, id;View examples
Count reachable verticesSELECT count(*) FROM reachable;View examples
Aggregate by depthSELECT depth, count(*) FROM walk GROUP BY depth ORDER BY depth;View examples
Index outbound edgesCREATE INDEX CONCURRENTLY edges_from_id_idx ON app.edges (from_id);View examples
Plan without traversalEXPLAIN WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n<10 ) SELECT * FROM t;View examples

WITH RECURSIVE evaluates an anchor term, then repeatedly feeds newly produced rows into a recursive term until no rows remain. It can express trees, reachability, paths, and iterative calculations, but termination, duplicate semantics, cycle handling, and result ordering must be designed explicitly.

Step by step

Detailed examples

01

Separate the anchor from the recursive step

The recursive CTE has a non-recursive anchor followed by UNION or UNION ALL and a recursive term that references the CTE once. Types are resolved across both terms, so cast anchor literals to the intended width. UNION ALL avoids duplicate-elimination work but requires another termination guarantee.

Walk descendants from one root
WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 0 AS depth
  FROM app.categories WHERE id = 42
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM app.categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT id, parent_id, name, depth FROM tree ORDER BY depth, id;
Back to quick reference ↑
02

Make termination a data invariant, not a hopeful LIMIT

A recursive term must eventually return no new rows. Outer LIMIT can help interactive testing because PostgreSQL often pulls rows lazily, but sorts and joins may consume the entire CTE and other systems differ; it is not a production cycle guard. Enforce depth, visited nodes, or CYCLE semantics inside the recursion.

Apply an explicit maximum depth
WITH RECURSIVE walk(id, depth) AS (
  SELECT 42::bigint, 0
  UNION ALL
  SELECT e.to_id, w.depth + 1
  FROM app.edges e JOIN walk w ON e.from_id = w.id
  WHERE w.depth < 20
)
SELECT id, depth FROM walk;

Note: A depth cap bounds work but may return a truncated graph; expose that fact to callers.

Back to quick reference ↑
03

Track visited vertices for path-specific cycle prevention

An array path can preserve traversal history and reject a vertex already present in the current path. For composite identity, use arrays of ROW values at extra memory and comparison cost. This prevents cycles per path but does not globally deduplicate vertices reached by different paths.

Return acyclic paths through a directed graph
WITH RECURSIVE walk(id, depth, path, is_cycle) AS (
  SELECT 42::bigint, 0, ARRAY[42::bigint], false
  UNION ALL
  SELECT e.to_id, w.depth + 1, w.path || e.to_id, e.to_id = ANY(w.path)
  FROM app.edges e JOIN walk w ON e.from_id = w.id
  WHERE NOT w.is_cycle
)
SELECT id, depth, path FROM walk WHERE NOT is_cycle;
Back to quick reference ↑
04

Use SQL CYCLE syntax for declarative detection

CYCLE names the identity columns to track and adds a cycle mark plus a path column. PostgreSQL rewrites it to path-based logic and stops expanding rows after a cycle is detected. The cycle-closing row can remain in output with is_cycle=true, so filter it when consumers expect only acyclic paths.

Detect graph cycles declaratively
WITH RECURSIVE walk(id, depth) AS (
  SELECT 42::bigint, 0
  UNION ALL
  SELECT e.to_id, w.depth + 1
  FROM app.edges e JOIN walk w ON e.from_id = w.id
) CYCLE id SET is_cycle USING path
SELECT id, depth, path FROM walk WHERE NOT is_cycle
ORDER BY path;
Back to quick reference ↑
05

Compute display order without confusing it with evaluation order

SEARCH DEPTH FIRST or BREADTH FIRST adds an ordering column; ordering the outer SELECT by that column controls presentation. PostgreSQL recursive evaluation is internally iterative and its emitted order is implementation-dependent. Add deterministic tie-breakers when peers can share equivalent search keys.

Order a hierarchy breadth-first
WITH RECURSIVE tree(id, parent_id, name) AS (
  SELECT id, parent_id, name FROM app.categories WHERE id = 42
  UNION ALL
  SELECT c.id, c.parent_id, c.name
  FROM app.categories c JOIN tree t ON c.parent_id = t.id
) SEARCH BREADTH FIRST BY id SET bfs_order
SELECT id, parent_id, name FROM tree ORDER BY bfs_order, id;
Back to quick reference ↑
06

Choose whether results represent vertices, edges, or complete paths

UNION removes duplicate complete rows, so adding depth or path can prevent vertex deduplication. For reachability, project only vertex identity in the recursive union; for every path, retain path state and expect combinatorial growth. Weighted shortest paths generally need specialized algorithms or extensions rather than naive exhaustive recursion.

Return unique reachable vertices
WITH RECURSIVE reachable(id) AS (
  VALUES (42::bigint)
  UNION
  SELECT e.to_id
  FROM app.edges e JOIN reachable r ON e.from_id = r.id
)
SELECT id FROM reachable ORDER BY id;

Note: UNION makes the finite vertex identity the deduplication key.

Back to quick reference ↑
07

Index edges and inspect recursive work at realistic scale

Index the join key used to find the next frontier, such as edges(from_id). EXPLAIN ANALYZE exposes Recursive Union loops, rows, buffers, and spills, but executes the traversal. A statement sees one MVCC snapshot, so concurrent committed graph changes normally are not mixed into a single read; SERIALIZABLE may still abort on conflicts.

Inspect a bounded traversal plan
EXPLAIN (ANALYZE, BUFFERS)
WITH RECURSIVE walk(id, depth) AS (
  VALUES (42::bigint, 0)
  UNION ALL
  SELECT e.to_id, w.depth + 1 FROM app.edges e JOIN walk w ON e.from_id = w.id
  WHERE w.depth < 10
)
SELECT count(*) FROM walk;

Note: Use a read-only transaction and representative graph; watch loops, row growth, temp I/O, and timeout behavior.

Back to quick reference ↑

Keep exploring

More SQL cheat sheets

Sources and further reading

References

Authoritative documentation used to verify and expand this cheat sheet.

  1. PostgreSQL Global Development GroupWITH Queriespostgresql.org
  2. PostgreSQL Global Development GroupSELECTpostgresql.org
  3. PostgreSQL Global Development GroupSorting Rowspostgresql.org
  4. PostgreSQL Global Development GroupArray Functions and Operatorspostgresql.org
  5. PostgreSQL Global Development GroupEXPLAINpostgresql.org
  6. PostgreSQL Global Development GroupIntroduction to MVCCpostgresql.org
  7. PostgreSQL Global Development GroupIndex Typespostgresql.org

Help us improve

Found a typo or missing example?

Tell us what would make this cheat sheet clearer, more complete, or more useful.

Share feedback