Stopping recursion

Learn when a recursive CTE stops.

Stopping recursion

In a recursive CTE the condition goes in the recursive part's WHERE. It stops when the condition fails.

SELECT n + 1 FROM countdown WHERE n < 5

This query stops when n reaches 5.

n + 1 is selected from countdown. WHERE keeps only the rows where n < 5 holds.

Stopping recursion — details

Without a condition, infinite recursion occurs. Databases usually guard with a depth limit.

You put the stop condition in the WHERE of the recursive part (e.g. WHERE n < 5). In PostgreSQL, forgetting it can make the query run until memory runs out.

Exercise Stopping recursion

Ensure stopping with a WHERE n < 5 condition in the recursive CTE.

Show example solution

SELECT n + 1 FROM countdown WHERE n < 5

Test yourself

Add WHERE

SELECT n + 1 FROM countdown ____ n < 5

Show answer

SELECT n + 1 FROM countdown WHERE n < 5

Stopping recursion: match the terms

condition

stop
WHERE

filter
infinite

danger
limit

depth
When does a recursive CTE stop?
- Never
- Always
- When the condition is no longer met
- Automatically
Show answer

When the condition is no longer met — It stops when the condition fails.

Next lesson: Summary - Recursive CTE

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies