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 would you list all reports under a manager, at any depth, using SQL?
Use a recursive CTE: start with the manager's direct reports, then repeatedly join employees whose manager_id matches the previous level. Track depth and a path array to show levels and guard against cycles.
IntermediateQuick 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 - What is a data-modifying CTE and how can DELETE ... RETURNING feed an INSERT?
PostgreSQL lets INSERT, UPDATE and DELETE appear inside WITH, and RETURNING hands the affected rows to the next step. This moves rows between tables in one atomic statement, for example archiving deleted rows.
IntermediateQuick answer1 min read