Generating Series and Sequences
Create rows with recursion.
What Is a Generated Series?
Sometimes you need a list of numbers, dates, or other sequential values that do not exist in any table. SQL lets you create these on the fly using recursion or built-in functions.
In this lesson you will learn how to generate sequences using WITH RECURSIVE — a powerful tool that lets a query refer to its own output.
Your First Recursive CTE
A recursive CTE (Common Table Expression) has two parts joined by UNION ALL: a base case that produces the first row, and a recursive step that references the CTE itself to produce the next row.
The recursion stops when the recursive step returns no rows.
WITH RECURSIVE counter(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter;All lessons in this course
- How Recursive CTEs Work
- Walking a Category Tree
- Generating Series and Sequences
- Avoiding Infinite Loops