generate_series

A set-returning function that produces a sequence of numbers or timestamps, e.g. a calendar.

Syntax

generate_series(start, stop [, step])                 -- integers or numerics
generate_series(start_ts, stop_ts, interval '1 day')  -- timestamps
SELECT ... FROM generate_series(...) AS g(alias) [WITH ORDINALITY]

generate_series returns one row per value from start to stop inclusive, in steps of step (default 1 for numbers; required for timestamps).

Use it in FROM like a table: FROM generate_series(1, 10) AS g(n).
Date spine: generate every day/month in a range and LEFT JOIN the facts to it, so periods with no activity appear with 0 instead of being missing.
With dates, generate_series(date '2025-01-01', date '2025-12-01', interval '1 month') returns timestamps; cast with ::date.
WITH ORDINALITY adds a row number column.
A negative step counts down; if the step goes the wrong way, no rows are returned.

Examples

Numbers and dates starter

SELECT n, n * n AS square
FROM generate_series(1, 5) AS g(n);

Five rows, 1 to 5 inclusive.

Month spine with zero-filled gaps starter

SELECT m::date AS month, count(o.order_id) AS orders
FROM generate_series(DATE '2025-01-01', DATE '2025-12-01', INTERVAL '1 month') AS g(m)
LEFT JOIN orders o
  ON o.order_date >= m AND o.order_date < m + INTERVAL '1 month'
GROUP BY m
ORDER BY m;

Every month of 2025 appears; months without orders show 0 (count of a column skips the NULLs from the LEFT JOIN).

Daily calendar for a week starter

SELECT d::date AS day, to_char(d, 'Dy') AS weekday
FROM generate_series(TIMESTAMP '2025-09-20', TIMESTAMP '2025-09-26', INTERVAL '1 day') AS g(d);

Seven rows; the stop value is included.

Pitfalls

Using count(*) after the LEFT JOIN: empty periods then count 1, not 0. Count a column from the joined table.
Forgetting the step for timestamps (there is no default).
Joining on equality with a timestamp spine (o.ordered_at = m) instead of a range or date_trunc match.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

SQL Server: GENERATE_SERIES(start, stop [, step]) for numbers since 2022; for dates generate numbers and use DATEADD.
BigQuery: GENERATE_ARRAY / GENERATE_DATE_ARRAY combined with UNNEST.
Snowflake: TABLE(GENERATOR(ROWCOUNT => n)) with SEQ4() or ROW_NUMBER().
MySQL: No generate_series; use a recursive CTE.