Recursive CTEs & Analytics — Series 2

Preview — 3 of 10 questions

A recursive CTE traversing a large tree accidentally used UNION instead of UNION ALL. What's the practical consequence?

javascript

WITH RECURSIVE tree AS (
  SELECT id, parent_id FROM nodes WHERE parent_id IS NULL
  UNION
  SELECT n.id, n.parent_id FROM nodes n JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree;

AIt still works, but PostgreSQL must deduplicate the accumulated result set on every iteration to check for termination, which is significantly slower than UNION ALL on large result sets — though it can also be a legitimate way to guarantee termination on a graph with cycles, since duplicate rows simply stop being added

BThe query is syntactically invalid — recursive CTEs must use UNION ALL, never plain UNION

CUNION and UNION ALL produce identical results and performance inside a recursive CTE

DUsing UNION causes the recursion to run exactly once, ignoring the recursive term entirely

What does this query do?

javascript

SELECT p.name, tag
FROM products p, LATERAL unnest(p.tags) AS tag;

AIt's a syntax error — LATERAL can only be used with subqueries, not functions

BIt returns one row per (product, tag) pair — for each product, unnest() expands its tags array into separate rows, each paired with that product's name

CIt returns one row per product, with all tags combined into a single array column

DIt filters products to only those with at least one tag

How do you expand a JSONB array of objects into a set of rows with typed columns?

javascript

SELECT * FROM jsonb_to_recordset(
  '[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]'::jsonb
) AS t(id int, name text);

Ajsonb_to_recordset only works on a single JSON object, not an array

BThe column definition list AS t(id int, name text) is optional and inferred automatically

Cjsonb_to_recordset takes a JSONB array of objects and a column definition list, and returns one row per array element with the specified columns extracted and cast to the given types

Djsonb_to_recordset requires the tablefunc extension to be enabled

Answer all 10 questions (7 more), see explanations for every answer, and track your score.