Recursive CTEs in PostgreSQL

You know the feeling: you need to list every employee in an org chart, every part in a product structure, or every node in a comment thread — and the obvious SQL just … stops. Joins flatten data, but hierarchical data isn't flat. Without the right tool, you end up writing recursive application code, firing query after query, or maintaining painful nested loops. That's exactly the pain this lesson solves: running recursive queries with CTEs in PostgreSQL, using the powerful WITH RECURSIVE clause to turn a single, readable query into a tree-walking engine.

The problem this lesson solves

Hierarchical data is everywhere: employee reporting lines, category trees, file systems, comment reply chains, and bill-of-materials structures. A plain JOIN can only reach one level deep at a time. To climb deeper, you'd write LEFT JOIN after LEFT JOIN until your query is a vertical cliff of repeated joins — and even then, it breaks the moment the depth varies. This is the classic "how many levels deep?" problem, and it's the reason recursive CTEs exist. Without them, developers often fall back to fetching data in loops, which is slow, chatty, and hard to maintain. Your database can do this traversal natively — you just need to know the syntax.

Core concept / mental model

Think of a recursive CTE as a loop written in SQL. It has two halves that mirror a function with a base case and a recursive call:

• Anchor member: the starting rows — the "base case" that seeds the loop.
• Recursive member: the query that references the CTE itself, discovering the next level of rows.

Each iteration takes the rows from the previous iteration, runs the recursive member against them, and adds new rows to the result. The loop repeats until the recursive member returns zero rows. A UNION ALL (or UNION) combines the anchor and recursive results, and a LIMIT or depth guard keeps runaway loops in check.

A diagram-in-words: imagine dropping a stone into a well. The anchor member is the stone hitting the water — your starting row. Each splash creates ripples (recursive iterations) that spread outward and trigger the next ripple, until the well is calm (no more rows).

The syntax looks like this:

WITH RECURSIVE cte_name AS (
    -- Anchor member
    SELECT ...
    UNION ALL
    -- Recursive member (references cte_name)
    SELECT ...
)
SELECT * FROM cte_name;

The RECURSIVE keyword is what turns a regular CTE into a self-referencing loop. Without it, PostgreSQL throws an error if you try to reference the CTE name inside its own body.

How it works step by step

Let's unpack the mechanics with a concrete scenario: an employee table where each row has an id, a name, and a manager_id pointing to the manager's row. This is the classic org-chart case.

1. Identify the starting point(s) — usually rows with a NULL parent (the CEO) or a specific node (e.g., an employee whose team you want).
2. Write the anchor query — SELECT those starting rows. These seed the CTE.
3. Write the recursive query — SELECT the children of the current iteration by joining the CTE's output to the parent column. This is where the magic happens: the CTE "sees" its own previous results.
4. Combine with UNION ALL — this appends each iteration's rows. Use UNION if you need to deduplicate (slower, usually unnecessary).
5. Add a depth column — increment a depth counter each iteration to track levels. This is invaluable for debugging and for limiting depth.
6. Add a termination guard — in a tree without cycles, the loop naturally ends when no children remain. But if your data has cycles (e.g., two employees manage each other), you need a LIMIT or a depth < X condition to avoid infinite loops.

The sequence is: anchor rows are produced first, then iteration 1 runs the recursive member against them, producing iteration 2's rows, and so on. The final result is the union of all iterations.

Hands-on walkthrough

Let's build a realistic example. First, create a table and seed it with data:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INT REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Alice', NULL),      -- CEO
    (2, 'Bob', 1),
    (3, 'Charlie', 1),
    (4, 'Diana', 2),
    (5, 'Eve', 2),
    (6, 'Frank', 3),
    (7, 'Grace', 4);

Now, run a recursive query to list everyone under Alice, including their depth:

WITH RECURSIVE org_chart AS (
    -- Anchor: the top of the tree
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: children of the previous iteration
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY depth, id;

Output:

 id |  name   | manager_id | depth
----+---------+------------+-------
  1 | Alice   |            |     0
  2 | Bob     |          1 |     1
  3 | Charlie |          1 |     1
  4 | Diana   |          2 |     2
  5 | Eve     |          2 |     2
  6 | Frank   |          3 |     2
  7 | Grace   |          4 |     3

Notice how the query automatically walks all levels — no hardcoded joins. Now let's find the path from Alice to Grace using a path column:

WITH RECURSIVE paths AS (
    SELECT id, name, manager_id, name AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id, p.path || ' → ' || e.name
    FROM employees e
    JOIN paths p ON e.manager_id = p.id
)
SELECT * FROM paths WHERE id = 7;

Output:

 id | name  | manager_id | path
----+-------+------------+------------------------------
  7 | Grace |          4 | Alice → Bob → Diana → Grace

This is the power of running recursive queries with CTEs — you get deep hierarchy and paths in one pass.

Adding cycle protection

If your data could contain cycles, add a depth guard:

WITH RECURSIVE org_chart AS (
    SELECT id, name, manager_id, 1 AS depth, ARRAY[id] AS path
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, oc.depth + 1, oc.path || e.id
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
    WHERE oc.depth < 10  -- hard limit
      AND NOT e.id = ANY(oc.path)  -- cycle detection
)
SELECT * FROM org_chart;

The path array tracks visited nodes; the WHERE NOT e.id = ANY(oc.path) stops cycles from repeating.

Pro tip: Always include a depth limit or cycle guard in production recursive queries. Even if your data is a clean tree today, one bad row can turn your query into an infinite loop that wrecks performance.

Compare options / when to choose what

Recursive CTEs aren't the only way to handle hierarchies. Here's a comparison:

Recursive CTEs shine when you need an occasional deep dive into a tree and you don't want to maintain a denormalized structure. For very large, hot traversals, consider a materialized path or nested sets.

Variations you might see

• UNION vs UNION ALL: UNION deduplicates rows across iterations, which can hide cycles but slows performance. Use UNION ALL unless you have duplicate rows.
• Multiple anchor members: You can combine two SELECTs before the UNION ALL to seed from multiple starting points (e.g., two top managers).
• Recursive CTE in INSERT/UPDATE: You can use a recursive CTE as a data source for INSERT … SELECT, useful for building denormalized tables.

Troubleshooting & edge cases

Here are the most common failures and fixes:

Error: "recursive reference to query 'cte' must not appear within its non-recursive term"

You referenced the CTE in the anchor member. Only the recursive member (after UNION ALL) may reference the CTE name.

Infinite loop / query never ends

Your data has a cycle or your recursive member keeps finding rows. Fix: add a depth counter and a WHERE depth < N condition, or track visited paths with an array.

Wrong result: missing rows

Check your join condition in the recursive member. Common mistake: joining on e.id = oc.manager_id instead of e.manager_id = oc.id — you want children of the current set, not managers.

Performance degradation on deep trees

PostgreSQL runs each iteration as a separate step, so depth 1000 can be slow. Add a depth limit and consider UNION (dedup) if you have duplicates. Index the foreign key (manager_id) to speed up joins.

Recursion terminated due to "MaxRecursionDepth"

PostgreSQL has a default recursion limit of 1000. You can raise it with SET max_recursive_iterations = 2000; (configurable per session) but be careful — it's usually a sign your query needs a guard.

UNION ALL with cycles produces duplicate rows

If your tree has cycles, UNION ALL will add repeating rows. Use a path array and NOT e.id = ANY(path) to prune.

What you learned & what's next

You now understand the core idea behind running recursive queries with CTEs: a CTE that references itself to walk hierarchical data. You can explain the two-part structure (anchor + recursive member), complete a practical exercise listing an org chart, add depth and path tracking, and compare recursive CTEs with alternative modeling approaches. You also know how to guard against infinite loops and debug common errors.

The next lesson in the track builds on this skill — you'll learn how to use recursive CTEs to build hierarchical aggregation queries, like summing sales per region down a tree. That's a natural extension of what you just practiced.

Pro tip: Practice writing the WITH RECURSIVE pattern from memory — it's a common interview question and a daily tool for backend engineers.

Now run the examples above, then try modifying the query to start from a specific employee and list only their sub-tree. That's your mini exercise to lock in the concept.