Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Recursive Queries

Introduction
The very basics
5. Recursion explained: syntax 1
Infinite looping
Processing graphs with recursive CTE
Summary

Instruction

Great. As you could see, we got rows containing numbers from 1 to 10 without actually using any table. Now, it's time to explain the magic behind that query. Take another look at the template from the previous exercise:

recursion

In Postgresql, a recursive CTE starts with the words WITH RECURSIVE, followed by the CTE name and the list of columns in the parentheses. In this case, our CTE under the name counter has only one column, which is the previous_value (that is, the value shown in the previous step).

Each correctly constructed recursive CTE has three essential elements inside the parentheses: an anchor member, a recursive member and a terminator. Let's discuss each of them.

An anchor member is basically something (an anchor point) that we start with. As we want to show numbers from 1 to 10, we will start with the number 1. You could think of it as the smallest matryoshka doll which is so small that it has no other matryoshka doll inside. In our case, the anchor member is expressed as

SELECT 1

Then comes the recursive member. The recursive member tells us what to do in each recursive step. In the case of matryoshka dolls, this could be: create a bigger matryoshka doll which will have the previous matryoshka doll inside it. So there we go with numbers: given that you have all rows with numbers generated so far, add another row with a number that is bigger by 1 than the number in the row previously generated. In our case

SELECT previous_value + 1
FROM counter

At this point, the CTE counter refers to itself (FROM counter).

If we stopped at this point, our query would go on and on, and it would never stop. In our real-life examples, each recursion comes to an end: there is the biggest matryoshka doll that holds all smaller dolls inside itself, just as there is the biggest cone in a Romanesco broccoli.

We need a terminator in recursive CTEs as well: something that will eventually stop our recursion. In this case, it's 

WHERE previous_value < 10
This means: as long as the previous value is smaller than 10, continue with the recursion. But if you reach 10, don't add any new rows. When a recursive CTE has nothing new to add in a given step, it terminates.

Exercise

Click Next exercise to continue.