Q: Structure of a recursive CTE

A recursive CTE has two parts joined by UNION ALL. The anchor member runs once and seeds the initial result set. The recursive member references the CTE itself and runs repeatedly, adding rows each iteration. Recursion stops when the recursive member returns no new rows. Think of it like a loop with a base case - the anchor is the starting value, the recursive member is the loop body.

Q: Termination condition

Recursion stops naturally when the recursive member finds no new rows to add. For a hierarchy traversal, that happens when there are no more children. For a sequence, that happens when the WHERE condition fails. There is no automatic limit - if you have a cycle in your data and no cycle detection, the query will run until it hits the database's max recursion depth (PostgreSQL defaults to 1000 iterations) and then error.

Q: Recursive vs regular CTE

The defining characteristic is self-reference: the recursive member's FROM clause names the CTE itself. A regular CTE is just a named subquery - it runs once and produces a fixed result. The RECURSIVE keyword in WITH RECURSIVE is required in PostgreSQL to enable self-reference; without it, the database treats the CTE as a regular named subquery and raises an error if you try to reference it within itself.

Q: Best use cases for recursive CTEs

Recursive CTEs shine when the number of levels is not known in advance. A JOIN can handle one level, a self-join can handle two, but an arbitrarily deep hierarchy requires recursion. Other classic use cases include generating date series, computing transitive closures in graphs, and building bill-of-materials explosions. The other options are all better handled by simpler, non-recursive SQL.

Question 1

Generating a number sequence

Accepted Answer

The anchor produces n=1. The recursive member adds n+1 on each iteration while n < 4. So the iterations produce n=2, n=3, n=4. When n=4, the condition n < 4 is false and the recursion stops. The final result is 1, 2, 3, 4. This pattern - generating sequences without a table - is one of the most common uses of recursive CTEs.

Question 2

Structure of a recursive CTE

Accepted Answer

A recursive CTE has two parts joined by UNION ALL. The anchor member runs once and seeds the initial result set. The recursive member references the CTE itself and runs repeatedly, adding rows each iteration. Recursion stops when the recursive member returns no new rows. Think of it like a loop with a base case - the anchor is the starting value, the recursive member is the loop body.

Question 3

Termination condition

Accepted Answer

Recursion stops naturally when the recursive member finds no new rows to add. For a hierarchy traversal, that happens when there are no more children. For a sequence, that happens when the WHERE condition fails. There is no automatic limit - if you have a cycle in your data and no cycle detection, the query will run until it hits the database's max recursion depth (PostgreSQL defaults to 1000 iterations) and then error.

Question 4

Recursive vs regular CTE

Accepted Answer

The defining characteristic is self-reference: the recursive member's FROM clause names the CTE itself. A regular CTE is just a named subquery - it runs once and produces a fixed result. The RECURSIVE keyword in WITH RECURSIVE is required in PostgreSQL to enable self-reference; without it, the database treats the CTE as a regular named subquery and raises an error if you try to reference it within itself.

Question 5

Best use cases for recursive CTEs

Accepted Answer

Recursive CTEs shine when the number of levels is not known in advance. A JOIN can handle one level, a self-join can handle two, but an arbitrarily deep hierarchy requires recursion. Other classic use cases include generating date series, computing transitive closures in graphs, and building bill-of-materials explosions. The other options are all better handled by simpler, non-recursive SQL.