Aaron is a natural teacher and this course is the best introduction to Postgres I have come across. Lessons are easy to follow and he recommends some great tools for working with Postgres.Joel Drumgoole
Shorten dev cycles with branching and zero-downtime schema migrations.
I wanna show you one more thing about a set generating function, and that is a way that you can put them side by side without joining. Beause usually in a set generating function, you're not using the ordinality and you don't have some sort of primary key and foreign key that you can join on. Uou still might want to have 'em show up side by side. There's a way to do that.
If we have two set generating functions here, we'll do one through 10, that's one. Let's just copy this whole thing. And this is a little uninspired, but we'll get a better example here in a second. Right now I just wanna prove that it works. To get these things side by side, we can't do a cross join 'cause that's gonna produce too many rows. That's gonna produce a row for a row every row. It's just way too many rows. That's not actually what we want. We just wanna line 'em up side by side.
We can do select * from rows from, it seems like a typo for sure. Select * from rows from, and we can put our two set generating functions in here and Postgres will line them up for us. We have two generate series and of course, we can give it as T lower numbers, upper numbers. And now we have some nice column names. Hey, that's pretty cool, right? We've taken two set generating functions and we've put 'em side by side. If their links are different, that's fine, they'll just be null padded. And so the right one is much longer and the left one is null padded. That's totally fine.
Let's keep going and look at an example that maybe makes a little bit more sense. If you had a certain span of time, let's just say that we're gonna go from the beginning of the year to the end of the year, and you needed this in some other query or whatever. You wanna figure out what day of the year it is. We're gonna say, "one day," and we can go from one, and we'll just go up to, you know, 366 should cover it. But hey, let's go up to 380, and we'll call this T date and num and then we can select date. Date, that's probably a bad name, and num. There you go. You're gonna see a bunch of nulls down here at the bottom. Hey, it looks like 2024 is in fact a leap year. Is there a February 29th year? Huh? You learn something new every day. There is a February 29th. Cool. You see a bunch of nulls down at the bottom and you can just say, "where date is not null." Now you have your 366 rows.
What we've done here is generated a series of dates, generated a series of numbers, put 'em side by side, and then cast the date to an actual date instead of a timestamp tz. And now we have our nice little lookup table there. In this case, we probably could have gotten away with ordinality, since we started our generate series number at one. That's what ordinality does, is it puts an auto incriminating integer in there that starts at one. But look at this case here, no ordinality is going to save us.
We've got three different columns that we're gonna put side by side, and they are all arrays that we are going to unnest. So any set generating function can work here. It doesn't just have to be generate series. These items all line up, and we're going to unnest them and put them side by side. We get a nice little table there that we have given some nice names as well. Maybe not your most used Postgres feature, but now you know that it exists. Should you ever need to put two lists side by side, you can reach for rows from.