0Pricing
SQL Academy · Lesson

Generating Series and Sequences

Create rows with recursion.

Generating Series and Sequences is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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;

How Recursion Unrolls

It helps to picture each step separately. The base case seeds the table, then each recursive pass adds one more row until the WHERE condition fails.

For the query WHERE n < 5, the engine generates rows 1, 2, 3, 4, 5 and then stops because 5 < 5 is false.

WITH RECURSIVE steps(n, note) AS (
  SELECT 1, 'base case'
  UNION ALL
  SELECT n + 1, 'recursive step'
  FROM steps
  WHERE n < 4
)
SELECT n, note FROM steps;

Generating Even Numbers

You can change the step size simply by adding more than 1 in the recursive part. This produces every even number from 2 to 10.

The pattern is always: next_value = current_value + step.

WITH RECURSIVE evens(n) AS (
  SELECT 2
  UNION ALL
  SELECT n + 2 FROM evens WHERE n < 10
)
SELECT n FROM evens;

Counting Down

Recursion is not limited to counting up. Subtract instead of add and you get a descending sequence. Make sure the stopping condition uses > instead of < to avoid an infinite loop.

WITH RECURSIVE countdown(n) AS (
  SELECT 10
  UNION ALL
  SELECT n - 1 FROM countdown WHERE n > 1
)
SELECT n FROM countdown;

Generating a Date Range

One of the most practical uses of recursive CTEs is building a date series. You start from a specific date and keep adding one day until you reach an end date.

This is especially useful for filling gaps in reports — every date appears even if there is no data for that day.

WITH RECURSIVE dates(d) AS (
  SELECT DATE '2024-01-01'
  UNION ALL
  SELECT d + INTERVAL '1 day'
  FROM dates
  WHERE d < DATE '2024-01-07'
)
SELECT d FROM dates;

Filling Report Gaps with a Date Series

Imagine a sales table that only has rows on days when sales occurred. By joining a generated date series to the sales table with a LEFT JOIN, you get every day in the range, with NULL for days with no sales.

WITH RECURSIVE cal(d) AS (
  SELECT DATE '2024-03-01'
  UNION ALL
  SELECT d + INTERVAL '1 day' FROM cal WHERE d < DATE '2024-03-05'
),
sales(sale_date, amount) AS (
  VALUES
    (DATE '2024-03-01', 100),
    (DATE '2024-03-03', 250),
    (DATE '2024-03-05', 180)
)
SELECT cal.d, COALESCE(sales.amount, 0) AS amount
FROM cal
LEFT JOIN sales ON cal.d = sales.sale_date
ORDER BY cal.d;

Generating a Multiplication Table

Recursive CTEs can carry multiple columns, allowing you to build richer outputs. Here both a row index and a computed value are tracked at the same time.

WITH RECURSIVE mult(n, result) AS (
  SELECT 1, 1 * 7
  UNION ALL
  SELECT n + 1, (n + 1) * 7
  FROM mult
  WHERE n < 10
)
SELECT n, result AS seven_times_n FROM mult;

Fibonacci Numbers

Fibonacci is a classic sequence where each number is the sum of the two before it: 0, 1, 1, 2, 3, 5, 8 …

A recursive CTE tracks both the current value a and the next value b, swapping them on each step.

WITH RECURSIVE fib(a, b) AS (
  SELECT 0, 1
  UNION ALL
  SELECT b, a + b FROM fib WHERE a < 100
)
SELECT a AS fibonacci FROM fib;

Using generate_series in PostgreSQL

PostgreSQL offers a built-in shortcut called generate_series() that produces sequences without needing to write a recursive CTE. It accepts a start, a stop, and an optional step.

This is the most concise way to generate number or date ranges in PostgreSQL.

SELECT gs AS num
FROM generate_series(1, 10, 2) AS gs;

Generating Monthly Intervals

Pass an interval of '1 month' to generate_series() to build a monthly calendar. This is perfect for creating monthly report headers or checking which months have no data.

SELECT gs::DATE AS month_start
FROM generate_series(
  '2024-01-01'::DATE,
  '2024-06-01'::DATE,
  INTERVAL '1 month'
) AS gs;

Knowledge Check

Test your understanding of generating series with recursive CTEs.

Recap: Generating Series and Sequences

In this lesson you learned how to produce sequences of numbers and dates without any source table:

  • WITH RECURSIVE — a base case plus a recursive step joined with UNION ALL; stops when the recursive step returns no rows.
  • Flexible step size — add or subtract any value to count up, count down, or skip values.
  • Date ranges — add an interval to generate daily, monthly, or custom calendar series.
  • Multi-column CTEs — carry extra state across iterations for richer outputs like Fibonacci.
  • generate_series() — PostgreSQL built-in for the most concise number and date sequences.

Generated series are essential for gap-filling in reports, building test data, and any situation where you need a complete range of values regardless of what the data contains.

Frequently asked questions

Is the “Generating Series and Sequences” lesson free?

Yes — the full text of “Generating Series and Sequences” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Generating Series and Sequences”?

Create rows with recursion. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Generating Series and Sequences” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. How Recursive CTEs Work
  2. Walking a Category Tree
  3. Generating Series and Sequences
  4. Avoiding Infinite Loops
← Back to SQL Academy