Recursive CTEs

A recursive CTE is a Common Table Expression that references itself in its own definition. It has two parts separated by UNION ALL: an anchor member (a regular SELECT that runs once and seeds the recursion) and a recursive member (a SELECT that references the CTE by name and joins the previous iteration's output to the base table). The database alternates between running the recursive member against the previous iteration's rows and accumulating results, until the recursive member produces zero new rows.

Use a recursive CTE when the data has a hierarchical or graph structure stored in a self-referencing table — a parent_id column that points back to the same table's primary key. The key advantage over self-joins: recursive CTEs traverse hierarchies of unknown depth. A fixed chain of self-joins works only when you know the exact number of levels in advance. Recursive CTEs adapt automatically to any depth.

Common use cases: organisational hierarchies (employee → manager relationships), product category trees (category → parent category), bill of materials (product → components → sub-components), network/graph traversal (finding paths between nodes), and generating series (sequences of numbers or dates). Any time you find yourself writing a chain of self-joins to handle levels of a hierarchy, replace it with a recursive CTE — the query becomes shorter, more correct, and depth-independent.

The anchor member is the first SELECT in the recursive CTE — the one that does not reference the CTE itself. It runs exactly once when the CTE begins executing and produces the initial set of rows that seeds the recursion. For a top-down hierarchy traversal, the anchor typically selects root nodes (WHERE parent_id IS NULL). For a bottom-up traversal, the anchor selects a specific leaf node to start from. For a number series, the anchor selects the starting value (SELECT 1 AS n).

The recursive member is the second SELECT — it references the CTE by name and joins the CTE's rows (which represent the previous iteration's output) to the base table to find the next level of rows. Each iteration of the recursive member sees only the rows produced by the immediately preceding iteration — not all accumulated rows. The recursive member must produce fewer and fewer rows as the recursion progresses, eventually producing zero rows to terminate.

The two members are connected by UNION ALL (not UNION). Using UNION would deduplicate across iterations, which is both semantically wrong for most hierarchies and significantly slower. The final result of the CTE is the UNION ALL of all rows produced by all iterations — the anchor's rows plus every recursive iteration's rows combined. The outer SELECT that queries the CTE reads this complete accumulated result set.

Infinite recursion occurs when following parent-child links leads back to a node already visited (a cycle: A → B → C → A) or when the termination condition is never satisfied. Without protection, the recursive member keeps producing rows forever — PostgreSQL enforces a default max_recursion_depth of 100 and raises an error when hit, but the error is less useful than intentional termination.

Two protection mechanisms. Method 1: depth limit — add a depth counter to the anchor (0 AS depth) and increment it in the recursive member (depth + 1). Add WHERE depth < N to the recursive member. The recursion stops after N iterations regardless of data. Simple and cheap, but N must be chosen carefully — too low and valid deep hierarchies are cut off, too high and cycles spin for N iterations before stopping. For most org charts N = 10–20 is safe; for BOMs N = 5–8 is typical.

Method 2: visited array — maintain an array of node IDs seen in the current path. In the anchor, initialise it with ARRAY[id]. In the recursive member, append the new node's ID and add WHERE NOT (new_id = ANY(visited_array)). This stops the moment a cycle is detected, at exactly the right point regardless of depth. Cost: the array grows with depth and IS DISTINCT FROM comparisons are slightly more expensive than integer comparison. For production code on data that might have cycles, use both: the visited array for correctness and a depth limit as a safety backstop.

The pattern: anchor on the start date, recursive member adds one day per iteration, terminate when the date reaches the end date. WITH RECURSIVE date_series AS (SELECT '2024-01-01'::DATE AS dt UNION ALL SELECT dt + 1 FROM date_series WHERE dt < '2024-01-31') SELECT dt FROM date_series.

The primary use of date series in analytics is filling gaps in time-series data. When querying daily revenue, days with no orders produce no rows — the result has gaps. A date series CTE produces every calendar date, and a LEFT JOIN to the revenue data fills in zero for missing days. This ensures reports show a complete timeline without gaps, which is essential for charts and moving averages that depend on consecutive date sequences.

For monthly series, add INTERVAL '1 month' instead of +1: SELECT '2024-01-01'::DATE AS m UNION ALL SELECT (m + INTERVAL '1 month')::DATE FROM months WHERE m < '2024-12-01'. PostgreSQL also provides the built-in generate_series(start, end, interval) function which is more concise for this purpose, but the recursive CTE pattern is portable across all SQL databases that support WITH RECURSIVE (PostgreSQL, SQL Server, MySQL 8+, SQLite 3.35+).

A self-join joins a table to itself on a specific relationship — typically parent_id = id. A single self-join produces one level of the hierarchy: employees joined to their direct managers. Two self-joins produce two levels. N self-joins produce N levels. The critical limitation: the number of self-joins must be known and hardcoded. If the hierarchy deepens from 4 to 5 levels, the query must be rewritten to add another self-join. This is brittle — any schema change to the depth of the hierarchy requires a query change.

A recursive CTE traverses any depth automatically. The recursive member defines the relationship between one level and the next. The database applies this definition repeatedly until no new rows are produced. A 4-level hierarchy and a 40-level hierarchy use the identical query — only the data depth differs, not the SQL. This makes recursive CTEs the correct tool for any hierarchy where the depth is variable, unknown at query time, or changes over time.

Performance comparison: for shallow, fixed-depth hierarchies (always exactly 2-3 levels), self-joins can be marginally faster because the query plan is simpler and the planner can optimise fully. For variable-depth hierarchies, recursive CTEs are both more correct (they handle any depth) and practically faster (a 5-level self-join requires 5 table scans with Cartesian intermediate results; a recursive CTE performs one scan per level with only the relevant rows). The readability advantage strongly favours recursive CTEs — 10 lines replacing 50 lines of chained self-joins, with no depth limitation.

Cause: The recursive member of the CTE does not reference the CTE's own name. A recursive CTE requires the second SELECT (after UNION ALL) to join to the CTE itself — that is what makes it recursive. Without the self-reference, PostgreSQL raises this error because the CTE structure is syntactically recursive (WITH RECURSIVE) but semantically it is just a regular CTE.

Fix: Ensure the recursive member contains a FROM or JOIN that references the CTE by name: WITH RECURSIVE org AS (SELECT ... FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.* FROM employees AS e JOIN org ON e.manager_id = org.employee_id). The JOIN org ON ... is the self-reference. If the query genuinely does not need recursion, remove RECURSIVE from WITH RECURSIVE — a non-recursive CTE does not require the keyword and can use UNION instead of UNION ALL.

Cause: The recursive CTE ran 100 iterations without the recursive member producing zero rows. Either the data contains a cycle (A is parent of B, B is parent of A), there is no termination condition in the WHERE clause of the recursive member, or the hierarchy is genuinely deeper than 100 levels.

Fix: Add a depth counter and termination condition: 0 AS depth in the anchor, depth + 1 in the recursive member, and WHERE depth < 50 in the recursive member's WHERE clause. For cycle detection, add a visited array: ARRAY[id] in the anchor, visited || new_id in the recursive member, and WHERE NOT (new_id = ANY(visited)) in the WHERE clause. To increase the limit temporarily (not recommended for production): SET max_recursion_depth = 200. Always prefer fixing the data or adding a proper termination condition over raising the limit.

Cause: The hierarchy data contains a node with multiple parents (a diamond pattern: A → B → D and A → C → D), causing the recursive member to reach node D twice via different paths. In a true tree (each node has exactly one parent), this should not happen — its presence indicates either bad data or that the data is a directed acyclic graph (DAG) rather than a tree.

Fix: If the data is a DAG (multiple parents allowed), use UNION instead of UNION ALL between anchor and recursive members — UNION deduplicates and ensures each node appears only once in the result. Note: UNION is slower than UNION ALL because deduplication requires sorting or hashing. Alternatively, use a visited array to skip nodes already accumulated. If the data should be a tree and duplicates indicate bad data, audit the parent_id assignments: SELECT child_id, COUNT(DISTINCT parent_id) FROM hierarchy_table GROUP BY child_id HAVING COUNT(DISTINCT parent_id) > 1 — any result here is a node with multiple parents.

Cause: The cumulative quantity is not being multiplied through the hierarchy correctly. A common mistake: using qty_per_parent instead of cumulative_qty * qty_per_parent for the child's cumulative quantity. If a product contains 3 packs and each pack contains 4 bottles, the correct total is 12 bottles — but if the recursive member uses only 4 (qty_per_parent of the pack) instead of 3 × 4 = 12 (parent's cumulative_qty × child's qty_per_parent), the total is wrong.

Fix: In the recursive member, compute the child's cumulative quantity as parent.cumulative_qty * child.qty_per_parent — always multiply by the parent's cumulative quantity, not just the level's quantity. Verify with a small known BOM: manually calculate the expected quantities for each component and compare to the CTE output. Add a CHECK column to the output showing the multiplication: parent_cumulative_qty || ' × ' || qty_per_parent || ' = ' || cumulative_qty to make the calculation visible and auditable.

Cause: The termination condition uses a strict less-than (<) when it should include the end date, or the date arithmetic is using INTERVAL in a way that skips non-existent dates (e.g., adding months to January 31 produces March 2 instead of February 28). Alternatively, the cast from INTERVAL addition back to DATE is missing, causing a TIMESTAMP result that does not match a DATE column in the LEFT JOIN.

Fix: For daily series: use WHERE dt <= '2024-01-31' (inclusive) or WHERE dt < '2024-02-01' (exclusive boundary). Both include January 31. For monthly series: use (month_start + INTERVAL '1 month')::DATE — the ::DATE cast is essential. PostgreSQL's INTERVAL '1 month' handles month-end correctly: '2024-01-31'::DATE + INTERVAL '1 month' = '2024-02-29' (in a leap year), not March 2. Always cast the result back to DATE to ensure the join condition with DATE columns works correctly.

Build two recursive CTEs using FreshCart data. (1) Write a recursive CTE that generates every date in the range of FreshCart's order data (from the MIN order_date to the MAX order_date), then LEFT JOIN it to orders to produce a complete daily revenue calendar showing: calendar_date, daily_revenue (0 if no orders that day), order_count (0 if none), and a 3-day moving average of daily_revenue using a window function on the result. (2) Write a recursive CTE that traverses the FreshCart employee table from root (manager_id IS NULL) downward, producing: employee_id, full_name, job_title, depth (0 for root), indented_name (REPEAT spaces + name), full_path (names joined by ' → '), and direct_report_count (how many employees have this person as their manager_id — compute with a subquery or join). Order the org chart by the path so siblings appear together.