How does a recursive CTE work? Show one that counts from 1 to 5.
A recursive CTE has an anchor query that produces the starting rows and a recursive query that refers to the CTE itself, joined with UNION or UNION ALL. PostgreSQL repeats the recursive step on the latest rows until it returns nothing.
Explain it simply
Think of a chain of messages: you read one, it points to the next, and you keep following until there is no next message. A recursive CTE does this inside SQL: start with a first row, then keep generating new rows from the previous ones until the step produces nothing.
Two parts are needed: the anchor (starting point) and the recursive term (how to get from one row to the next). Without a stopping condition it never ends.
Example
WITH RECURSIVE counter(n) AS (
SELECT 1 -- anchor
UNION ALL
SELECT n + 1 FROM counter -- recursive step
WHERE n < 5 -- stop condition
)
SELECT n FROM counter;
-- 1
-- 2
-- 3
-- 4
-- 5Execution: the anchor gives row 1. The recursive step sees only row 1 and produces 2. It then sees only 2 and produces 3, and so on. When n reaches 5 the WHERE rejects 6, the step returns nothing, and recursion stops.
What to say in the interview
"A recursive CTE is an anchor plus a recursive term joined by UNION ALL. Each iteration works on the rows from the previous one and stops when it produces none. I always include a terminating condition, because otherwise the query runs until memory or disk is exhausted."
Common mistakes
- Forgetting the WHERE that ends the recursion.