Interview Q&A / PostgreSQL
PostgreSQL interview questions
Indexes, EXPLAIN, transactions and isolation, JSONB and the Postgres specifics backend interviews probe.
Prefer rapid revision? Practise PostgreSQL short interview answers →
- 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.
BeginnerQuick answer1 min read - Should a recursive CTE use UNION or UNION ALL?
UNION ALL keeps every produced row and is faster. UNION removes duplicates against all rows found so far, which can stop some cycles but costs extra work. For trees, use UNION ALL; for graphs with possible loops, add explicit cycle protection.
IntermediateQuick answer1 min read