Menu

PostgreSQL generate_series: Numbers, Dates and Gaps

generate_series returns a set of rows from a start to a stop value: numbers, dates and timestamps. Use it to fill gaps in reports, generate test data and build calendars.

This page includes runnable editors. Edit, run, and see output instantly.

generate_series(start, stop, step) returns one row per value from start up to and including stop, moving by step (1 if you leave it out). It works with integers, numerics, and timestamps with an interval step. Use it in FROM like a table.

This returns 1, 4, 7 and 10 with their squares. A negative step counts down: generate_series(5, 1, -2) gives 5, 3, 1. A series that cannot reach its end, such as generate_series(5, 1), returns no rows. generate_series(0, 1, 0.25) steps through numerics.

Generate a series of dates

Pass timestamps or dates with an interval step. There is no date version, so the values come back as timestamptz; cast them with ::date:

Five days come back, from February 26 to March 2, across the month end. Any interval works as the step: interval '1 hour', interval '15 minutes', interval '1 month', interval '1 week'.

With timestamptz values, a step of interval '1 day' follows the calendar of the session's TimeZone. Across a daylight saving change, one step can be 23 or 25 hours. Since PostgreSQL 16, a fourth argument names the zone to step in, whatever the session uses:

SELECT generate_series(
    timestamptz '2026-03-28 00:00 Europe/Berlin',
    timestamptz '2026-03-31 00:00 Europe/Berlin',
    interval '1 day',
    'Europe/Berlin');

Fill missing days in a report

A GROUP BY over your data only returns periods that have rows. To show every day with a zero for days without orders, generate the days and LEFT JOIN the data to them:

November 1 has 2 orders, November 4 has 1, and the 2nd, 3rd and 5th show 0. Count a column of the joined table (count(o.id)), not count(*), or every empty day counts as 1.

Comparing created_at with a range, instead of created_at::date = d, lets an index on created_at be used. The same pattern with interval '1 month' and date_trunc fills empty months (see date functions).

Generate test data

INSERT ... SELECT from a series creates as many rows as you need in one statement:

The table gets 1,000 users spread over 30 sign-up days. The values are computed from i, so every run gives the same result; use random() when you want them to vary. Generate a million rows the same way to try an index and compare plans with EXPLAIN ANALYZE.

Number the rows and use series in joins

WITH ORDINALITY adds a position column to any set-returning function:

This lists five 15-minute slots from 09:00 to 10:00, numbered 1 to 5. A series also works in a CROSS JOIN, for example to pair every user with every day of a month.

generate_series in the SELECT list also returns several rows, but two such calls of different lengths pad the shorter one with NULLs. Keep set-returning functions in FROM. For series a single function cannot express, such as walking a tree, use a recursive CTE.

Frequently Asked Questions

Does generate_series include the end value?

Yes, when the steps land on it: generate_series(1, 5) returns 1 to 5. When they do not, the series stops at the last value that does not pass the end, so generate_series(1, 10, 4) returns 1, 5 and 9.

Does MySQL have generate_series?

No. In MySQL you build a series with a recursive CTE (WITH RECURSIVE), which also works in PostgreSQL but is longer to write.

Why does generate_series with dates return timestamps?

There is no date version of the function, so PostgreSQL uses the timestamptz one. Cast each value back with ::date.

How do I generate a random number for each row?

Call random() in the select list: SELECT i, random() FROM generate_series(1, 5) AS i gives a value between 0 and 1 for each row, and floor(random() * 100)::int an integer from 0 to 99. Since PostgreSQL 17, random(1, 100) returns a random integer in that range directly.

Coddy programming languages illustration

Learn to code with Coddy

Get started