Recursive CTEs

Our existing tables don't contain a hierarchical relationship, so we'll create a small temporary table for this chapter.

Run:

Now insert this hierarchy:

The data represents:

Each row contains only its immediate parent.

For example:

and category 4 is:

Suppose we start with:

and want to find every category underneath it.

We don't know beforehand how many levels the hierarchy contains.

It might be:

A fixed number of joins is therefore not a good solution.

This is where a recursive CTE is useful.

Run:

You should see:

The query discovered every level of the hierarchy.

A recursive CTE usually contains two main parts joined by:

The first is the non-recursive term:

This gives PostgreSQL the rows to start with.

In our example, it returns:

The second part is the recursive term:

Notice that this part references:

inside the definition of category_tree itself.

That self-reference is what makes the CTE recursive.

Although the syntax is recursive, PostgreSQL evaluates a recursive CTE iteratively.

Let's follow our example.

First step. The non-recursive term finds:

That becomes the starting result.

Next iteration. PostgreSQL runs the recursive term using Electronics.

It looks for rows where:

and finds:

Those become the next working rows.

Next iteration. PostgreSQL now looks for children of:

and finds:

Next iteration. It looks for children again and finds:

Final iteration. Gaming Laptops has no children.

The recursive term produces no new rows.

The process ends.

Conceptually:

A recursive CTE must eventually stop producing rows.

In our query, the relationship:

eventually reaches categories with no children.

At that point, the recursive term produces no rows and PostgreSQL stops iterating.

This is extremely important.

If the recursive logic continues producing rows indefinitely, the query can continue indefinitely.

We added:

to the starting row.

Then every recursive step uses:

This produces:

The depth column was not stored in demo_categories.

It is calculated while the recursive CTE traverses the hierarchy.

This is useful when we want to know how far each result is from the starting row.

The same query can start anywhere in the hierarchy.

For example, change:

to:

Now the traversal begins at:

and returns:

It does not return Phones because Phones is not a descendant of Computers.

Notice that depth is relative to the starting row, so Computers is now 0 rather than 1.

Recursive CTEs can use either:

or:

UNION ALL keeps every row produced by the recursion.

UNION removes duplicate rows.

For tree structures where each relationship is traversed once, UNION ALL is commonly appropriate and avoids unnecessary duplicate elimination.

Using UNION can help eliminate repeated identical rows in some recursive queries, but it should not be treated as a complete solution to every possible cycle.

Trees have a natural direction:

But graph-like data can contain cycles.

For example:

A recursive query that keeps following those relationships could revisit the same rows repeatedly.

For recursive queries over data where cycles are possible, the query needs a strategy for detecting previously visited rows.

PostgreSQL also provides more advanced features for search order and cycle detection, but those are beyond what we need for the core recursive CTE pattern.

The important thing to remember is:

A recursive query must have a path to termination.

If we knew that a hierarchy had exactly two levels, we could potentially solve it using a fixed number of joins.

Recursive CTEs become particularly useful when we don't know the depth in advance.

For example:

The query continues following the relationship until there are no more matching rows.

Remove the demonstration table:

A recursive CTE has:

The important point is:

Recursive CTEs let a query repeatedly follow relationships when the number of levels is not known in advance.