Quick summary
Summarize this blog with AI
Recursive CTEs: build and test one step at a time
A recursive common table expression starts with an anchor result, repeatedly follows a relationship, and stops when no new rows are produced. This is useful for hierarchy traversal and some sequence problems. Correctness depends on the relationship, compatible column types, and a termination rule.
These complete examples use PostgreSQL 16 and fictional in-query data. They read no stored tables and make no database changes. PostgreSQL documents recursive evaluation, search ordering, and cycle handling in its WITH query reference.
Think in successive frontiers
The anchor below produces employee 101 at depth zero. The recursive step takes the current frontier and finds employees whose manager belongs to it. That produces 102 and 103, then 104, then nothing. The accumulated output contains all four employees. The final ORDER BY controls display order; it does not control how the engine evaluates recursion.
Traverse descendants from a chosen employee
WITH RECURSIVE employees(employee_id, manager_id, full_name) AS (
VALUES (101, NULL::integer, 'Ari'), (102, 101, 'Bea'),
(103, 101, 'Chen'), (104, 102, 'Dia')
), org(employee_id, manager_id, full_name, depth, visited_ids, path) AS (
SELECT employee_id, manager_id, full_name, 0,
ARRAY[employee_id], full_name::text
FROM employees
WHERE employee_id = 101
UNION ALL
SELECT e.employee_id, e.manager_id, e.full_name, o.depth + 1,
o.visited_ids || e.employee_id,
o.path || ' > ' || e.full_name
FROM employees AS e
JOIN org AS o ON e.manager_id = o.employee_id
WHERE NOT (e.employee_id = ANY(o.visited_ids))
)
SELECT employee_id, manager_id, full_name, depth, path
FROM org
ORDER BY depth, employee_id;employee_id | manager_id | full_name | depth | path
101 | NULL | Ari | 0 | Ari
102 | 101 | Bea | 1 | Ari > Bea
103 | 101 | Chen | 1 | Ari > Chen
104 | 102 | Dia | 2 | Ari > Bea > DiaThe anchor uses NULL::integer to give the manager column a clear type and full_name::text to give the path a type compatible with concatenation. Avoid arbitrarily casting the anchor to a short varchar(n): recursive output must remain type-compatible, and the growing path can exceed an assumed length. With a stored table, employee IDs should be unique and non-null.
Choose descendants or ancestors
e.manager_id = o.employee_id moves from a manager to direct reports. To walk toward ancestors, join e.employee_id = o.manager_id and start at the employee whose managers you need. Test one step with a known pair before running the full traversal. Starting at all roots versus one selected ID changes the population.
Keep identity separate from display
The text path is for readers. The integer array tracks identity. Two people can share a name, so checking for repeated names in a text path could reject valid rows. Likewise, string substring searches can confuse ID 1 with ID 11. Track complete, typed identifiers.
Cycles can keep generating new rows
Imagine 101 reports to 103, 103 reports to 102, and 102 reports to 101. A traversal starting at 101 can return to 101 indefinitely. Replacing UNION ALL with UNION is not a general remedy: a row with a different depth or path is still a different output row.
Demonstrate a cycle explicitly
WITH RECURSIVE edges(parent_id, child_id) AS (
VALUES (1, 2), (2, 3), (3, 1)
), walk(node_id, visited_ids, depth) AS (
SELECT 1, ARRAY[1], 0
UNION ALL
SELECT e.child_id, w.visited_ids || e.child_id, w.depth + 1
FROM edges AS e
JOIN walk AS w ON e.parent_id = w.node_id
WHERE NOT (e.child_id = ANY(w.visited_ids))
)
SELECT node_id, visited_ids, depth FROM walk ORDER BY depth;node_id | visited_ids | depth
1 | {1} | 0
2 | {1,2} | 1
3 | {1,2,3} | 2The edge back to 1 is excluded because 1 already belongs to that path. This prevents revisiting a node within one path; it does not deduplicate a node reached through two different paths in a graph. Decide whether your output means paths or unique reachable nodes. A graph with many valid paths can still produce a large result even with cycle protection.
Keep DATE values as DATE values
In PostgreSQL, date + integer returns a date, while date + interval returns a timestamp. Mixing a date anchor with an uncast timestamp recursive expression can fail recursive type checking. The following query adds integer 1 to keep both terms as dates. The date/time operator reference specifies these result types.
WITH RECURSIVE subscriptions(subscription_id, start_date, end_date) AS (
VALUES
(1, DATE '2026-01-30', DATE '2026-02-01'),
(2, DATE '2026-02-10', DATE '2026-02-10'),
(3, DATE '2026-02-12', DATE '2026-02-11'),
(4, NULL::date, DATE '2026-02-11'),
(5, DATE '2026-02-10', NULL::date)
), subscription_days(subscription_id, day, end_date) AS (
SELECT subscription_id, start_date, end_date
FROM subscriptions
WHERE start_date IS NOT NULL AND end_date IS NOT NULL
AND start_date <= end_date
UNION ALL
SELECT subscription_id, day + 1, end_date
FROM subscription_days
WHERE day < end_date
)
SELECT subscription_id, day
FROM subscription_days
ORDER BY subscription_id, day;subscription_id | day
1 | 2026-01-30
1 | 2026-01-31
1 | 2026-02-01
2 | 2026-02-10The interval is inclusive at both ends. An equal start and end emits one day. Reversed ranges and null endpoints emit no rows by explicit policy; a production workflow should also report or reject those invalid inputs rather than silently lose them. Open-ended subscriptions need a deliberate as-of date. If your contract uses an exclusive end, change both the anchor eligibility and recursive bound consistently.
Debug a small fixture before real data
1. Verify the anchor
Run the non-recursive selection alone. Check starting IDs, date validity, row count, and column types. An empty anchor means an empty traversal even when child rows exist.
2. Verify one transition
Join the source to just the anchor rows. In the hierarchy fixture the first transition must produce 102 and 103, not Ari's manager or all employees.
3. Inspect depth and path
Depth starts at zero here. An unexpected repeat at a later depth usually signals a cycle, duplicate source relationship, or incorrect join. Inspect IDs rather than relying only on names.
4. Test cycle behavior
Use the three-edge fixture above. Confirm the traversal terminates and returns the expected rows. Separately test a self-loop, a missing root, and a graph where two paths reach the same node.
5. Use an explicit diagnostic limit
Adding AND o.depth < 10 to the hierarchy's recursive predicate allows rows through depth 10. It can prevent runaway debugging work, but a capped result may be incomplete. Do not present a depth-limited result as a complete hierarchy. Use a statement timeout and inspect rows at the cap.
6. Define duplicate semantics
Decide whether duplicate source edges are errors, whether distinct paths matter, and whether the final output should deduplicate node IDs. Do not change the set operator only to make an unexplained duplicate disappear.
Limit the problem before optimizing it
For stored hierarchy data, examine an index on the lookup column, such as manager_id for descendant traversal. Carry only the columns needed during recursion. Estimate maximum depth and branching, and profile on representative data. A cycle guard stops repeated nodes within a path; it does not bound the number of valid paths.
Port the complete query, including types
These examples are PostgreSQL-specific where they use arrays, ANY, casts, and date-plus-integer arithmetic. Other engines have different recursive syntax, recursion limits, and path representations. Verify a tiny fixture on the target engine before translating the production query.
Explain the invariant
For the hierarchy query: every output row is reachable from employee 101 by the manager-to-report relationship; its visited array contains the IDs on that path; and a recursive step adds only an ID absent from the array. That explains both the direction and the cycle guard.
Prefer simpler operations when available
A single self join is enough for one hierarchy level. For a regular date range, a calendar table or PostgreSQL's series-generation functions may be simpler. Recursion is most useful when each next step depends on the prior frontier rather than a fixed range.
FAQ
What is the difference between the anchor and recursive members?
The anchor produces the initial rows. The recursive member derives the next frontier from the current one. Their corresponding output columns must have compatible types.
Why does my recursive CTE return duplicates?
Possible causes include duplicate source edges, multiple valid paths to a node, a broad join, or a cycle. Inspect identities and paths to distinguish them before choosing a fix.
How do I stop an infinite traversal?
Use a termination rule appropriate to the relationship, such as a visited-ID check for cycles or a date bound for a sequence. A depth cap can protect a diagnostic run but may truncate legitimate results.
Should I use recursion for date generation?
It works for the bounded example here, but a calendar table or series generator is often simpler. Define inclusivity, invalid ranges, and open-ended dates regardless of the implementation.
