Now that we have covered CTEs, we gotta talk about recursive CTEs, which are somehow even cooler. A CTE, as we've covered, is a way to extract some logic, give it a pretty name, and reference it down below. A recursive CTE still follows that pattern, but there's a second part, which is the part where this CTE can run over and over, and even reference itself to build up some sort of result set. Let me show you a few examples.

A recursive CTE starts the same way, but instead of just saying numbers as, you know, select 1 and then select * from numbers, that's just a regular CTE. We're gonna say with recursive numbers as, and then this is the anchor condition, this is the beginning condition. This is the non-recursive part of the recursive CTE. You have that first part, and then you can do union, or in our case, we're gonna do union all, and we're gonna say select 2 from, we'll just say select 2, let's just start there. That's not actually recursive because we're not doing anything over and over and over again.

Let's carry on and say select 1 as n, and then select n+1 from the very table that we are constructing. Importantly, we're gonna say where n<10. You'll see that this part was run once. We have one value for n and then we have n+1, which is run over and over and over, referencing the value directly before it. In this case, this is the recursive condition, this is the anchor condition. This is the CTE, which is recursive. We're simply selecting from it here at the bottom.

Now in this case, this isn't much better. In fact, maybe it's worse than a generate series because Postgres does have that powerful generate series function, which you can use to generate regularly stepped series like this. In this case, we're just stepping by one each time, and a generate series could do this, but there's a lot of things that recursive CTEs can do that a generate series cannot do.

Let me show you one of those. In this case, what we want to do is instead of just having 1 as n, we'll have rand as, or we'll do 0 as rand. Actually, let's just start by saying, let's put a random number in there. We're gonna have a random number between 1 and 10. We're flooring the random() * 10, and then we're adding one on top of it. We want to go ahead and cast this to an integer. Here, the first, the anchor condition can declare the column aliases as well as the type. If we were then to say, let's do, you know, just .1 for this one that has been cast as an integer, it's going to tell you, "Recursive query numbers column 2 has an type integer in the non-recursive term, but type numeric overall." What this is telling us is in our non-recursive condition, it was either explicitly declared as an integer like this, or it just so happened that it was an integer in some cases, and then your recursive condition has to match that. We're gonna go ahead and just take this out.

What we're gonna do is instead of saying, I just want to select a random number, we want an ever-increasing range with random steps. We can do that. We can say, let's take our rand from the row above us, and then add in a random number between 1 and 10. We are gonna cast that entire thing to an integer. We have an ever-increasing sequence with random steps in between. If you wanted to change how big those random steps were, now you have an ever-increasing random sequence with random size steps, something that generate series could not do, something that a recursive CTE can do. Okay, it's kind of cool. It's also kinda lame.

Let's move forward and we're gonna calculate a Fibonacci sequence using a recursive CTE. It's a little bit cooler. I'm gonna change the style up a little bit. Instead of putting the column aliases down here, which is totally fine, I am gonna move them up here and we're gonna do id, a, and b. This can be easier to see kind of at a glance when you're down in this section of the query and you're looking back up to the numbers table and you're like, "Wait, what are these column names? Let me parse through. No, I'm just gonna look up here." Totally up to you, that is preference.

We're gonna say id is 1, and then we'll say 0 and 1, union all, union all, select id + 1. This is just gonna be an auto-incrementing id, and then we'll select b, and then a + b, a + b where id < 20. You always want to have some condition that terminates the recursion. The conditions that can terminate the recursion are you have a strict where right here, or this recursive condition doesn't produce any more rows. Once it doesn't produce any more rows, like you're all joined out, there's nothing left to join, which we'll look at in the hierarchical data example. When you don't produce any more rows, the recursion stops. If you don't stop the recursion, you're gonna be in a world of hurt. Stop your recursion. If we do this and then we run that there, we'll see, "Column id does not exist," because we didn't put a from. From numbers, and now we have our Fibonacci sequence, but only one of those columns is right. If we do id, and then a as fib, then we're looking good. We've got 0 and 1 make 1, 1 and 1 make 2, 1 and 2 make 3, 3 and 5 make 8. 8 and 13, 21, we did it.

That's just an intro into recursive CTEs. We are gonna do a hierarchical data structure example, but I'm gonna save that for the next video so we have a little more time to talk about it. But as a recap, recursive CTE, just like a regular CTE, except you add that keyword recursive, and then you have your anchor condition, the union or union all, and then the recursive condition, which will continue to generate rows. You can select from the very table you're building, which is crazy, but just make sure that at some point, the recursion ends, whether that's a strict where clause or as we'll see in the hierarchical data example, you don't have anything else to join to, and then the recursion will just stop.