Recursive CTEs
advancedadvanced
2 min read
What is a Recursive CTE?
A recursive CTE uses WITH RECURSIVE to define a query that references itself. It has two parts: a base case (anchor) and a recursive step. The query repeatedly executes the recursive step until no new rows are produced.
Syntax
WITH RECURSIVE cte_name AS (
-- Anchor: base case
SELECT columns FROM table WHERE condition
UNION ALL
-- Recursive step: references cte_name
SELECT columns FROM table JOIN cte_name ON ...
)
SELECT * FROM cte_name;When to Use
- Traversing hierarchical data (org charts, category trees)
- Generating sequences of numbers or dates
- Graph traversal (finding paths between nodes)
- Bill of materials (BOM) queries
Key Points
- Anchor + Recursive — The anchor provides starting rows; the recursive step adds more rows each iteration.
- UNION ALL — Use UNION ALL (most common) or UNION (eliminates duplicates, prevents infinite loops).
- Termination — Stops when the recursive step produces no new rows.
- Cycle Detection — In PostgreSQL 14+, use CYCLE to detect loops. Otherwise, add a depth limit.
- Performance — Add a maximum depth condition to prevent runaway recursion.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
Recursive CTEs Challenge
Write a query that solve this task: generate numbers 1 through 10 using a recursive CTE.
Expected result
Numbers 1 through 10, one per row.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is a Recursive CTE?
Initializing database...Each run starts from fresh sample data.
More Examples
Employee hierarchy
Traverse an employee-manager hierarchy.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Can a recursive CTE cause an infinite loop?
Yes, if the recursive step keeps producing rows. Use UNION (deduplicates), a depth limit, or PostgreSQL 14+ CYCLE clause.
Is WITH RECURSIVE expensive?
It depends on the depth and breadth of recursion. For shallow hierarchies it is efficient. For deep graphs, add limits and indexes.