Mastering Postgres
Introduction
Introduction to the course
Overview of course structure
Postgres vs. everyone
The psql CLI
Data Types
Introduction to schema
Integers
Numeric
Floating point
Storing money
NaNs and infinity
Casting types
Characters types
Check constraints
Domain types
Charsets and collations
Binary data
UUIDs
Boolean
Enums
Timestamps
Timezones
Dates and times
Advanced Data Types
Intervals
Serial type
Sequences
Identity
Network and mac addresses
JSON
Arrays
Generated columns
Text search types
Bit string
Ranges
Composite types
Nulls
Unique constraints
Exclusion constraints
Foreign key constraints
Indexing
Introduction to indexes
Heaps and CTIDs
B-Tree overview
Primary keys vs. secondary indexes
Primary key types
Where to add indexes
Index selectivity
Composite indexes
Composite range
Combining multiple indexes
Covering indexes
Partial indexes
Index ordering
Ordering nulls in indexes
Advanced Indexing
Functional indexes
Duplicate indexes
Hash indexes
Naming indexes
Understanding Query Plans
Introduction to explain
Explain structure
Scan nodes
Costs and rows
Explain analyze
Generating Results
Introduction to queries
Cross joins
Inner joins
Outer joins
Subqueries
Lateral joins
ROWS FROM
Filling gaps in sequences
Subquery elimination
Combining queries
Set generating functions
Indexing joins
Advanced SQL
Introduction to advanced SQL
Grouping
Grouping sets, rollups, cubes
Window functions
CTEs
CTEs with window functions
Recursive CTE
Hierarchical recursive CTE
Handling nulls
Row value syntax
Views
Materialized views
Removing duplicate rows
Upsert
Returning keyword
COALESCE + generated column
Full Text Search
Introduction to full text search
Searching with LIKE
Vectors, queries, and ranks
Websearch
Ranking
Indexing full text search
Highlighting
Advanced JSON
Intro to JSON
JSON vs JSONB
Validating JSON
Creating JSON objects + arrays
JSON extraction
JSON containment
JSON existence
JSON recordset
Updating JSON
Indexing JSON parts
GIN index
Vectors (pgvector)
Intro to pgvector
Vector embedding columns
Find related articles
Upsert vector embedding
Semantic search
Other operators
Vector indexes
Outro
Thank you
Bonus interviews
Heroku's glory days & Postgres vs the world (with Craig Kerstiens)
Creating a Postgres platform with Monica & Tudor from Xata.io
Bootstrapping an email service provider (with Jesse Hanley)
Locked video

Please purchase the course to watch this video.

Video thumbnail
Generating Results
ROWS FROM

Full Course

$
349
$399
USD, one-time fee
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
Joel Drumgoole

PostgreSQL database platform

Shorten dev cycles with branching and zero-downtime schema migrations.

Test Data

I've made my test data available for you to use and follow along.

Download
or
Use on Xata

Summary

Set-generating functions in Postgres can be used to display two lists side by side without requiring a join or key relationship. By using `SELECT * FROM rows FROM (...)`, you can align multiple functions into organized columns, with automatic null padding if the lists have different lengths. This technique is especially useful for scenarios like building date lookup tables or managing unrelated datasets in a structured format.

Video Transcript

I want to 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 want to 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 want to 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. 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 want to 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.