Interview Q&A / PostgreSQL

1. 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
2. What is a CTE (WITH clause) and why would you use one?

   A common table expression names a subquery at the top of a statement so you can read it in steps and reuse it more than once. It improves readability; since PostgreSQL 12 a simple CTE is usually inlined like a subquery.

   BeginnerQuick answer1 min read
3. 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
4. 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
5. 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
6. What do MATERIALIZED and NOT MATERIALIZED do on a CTE?

   They control whether PostgreSQL computes the CTE once and stores the result (MATERIALIZED) or folds it into the outer query like a subquery (NOT MATERIALIZED). Before version 12, every CTE was materialized and acted as an optimisation fence.

   AdvancedQuick answer1 min read