GENERATE_SERIES

Return a table of values from start to stop, incremented by step.

Category: miscReturns: TABLEDialect: PostgreSql

Syntax

GENERATE_SERIES(start, stop [, step])

Description

## Overview Returns a set of values from `start` to `stop` (inclusive) incremented by `step`. Use this table function to manufacture a sequence for pagination, to build calendar scaffolding, or to join against sparse data for gap-filling. For integer arguments, the default step is 1. For timestamp arguments, a step of INTERVAL '1 day' is used when step is omitted. ## Behavior - Returns a single-column set; each row is one value from the series. - If start > stop with a positive step (or start < stop with a negative step), no rows are returned. - Step of zero raises an error. - NULL arguments produce zero rows. - Deterministic for a given set of arguments. - Side effect free. ## Compatibility - PG-compat alias for `generate_series` set-returning function.

Parameters

Name	Type	Description
`start`		Specifies the starting value of the series (inclusive).
`stop`		Specifies the ending value of the series (inclusive).
`step`		Specifies the increment between values. Defaults to 1 for integer series and INTERVAL '1 day' for timestamp series. Must be non-zero.

Examples

-- Integers from 1 to 5
SELECT * FROM GENERATE_SERIES(1, 5);

-- Even numbers from 2 to 10
SELECT * FROM GENERATE_SERIES(2, 10, 2);

-- Descending integer series
SELECT * FROM GENERATE_SERIES(10, 1, -1);

-- Timestamp series at daily step
SELECT * FROM GENERATE_SERIES(DATE '2025-01-01', DATE '2025-01-07', INTERVAL '1 day');

-- Empty series when start > stop with positive step
SELECT * FROM GENERATE_SERIES(5, 1);  -- no rows

-- Join a series with calendar data to fill gaps
SELECT d.dt, COALESCE(c.count, 0) AS count
FROM GENERATE_SERIES(DATE '2025-01-01', DATE '2025-01-31', INTERVAL '1 day') AS d(dt)
LEFT JOIN obs.catalog.daily_counts c ON c.dt = d.dt;

Pitfalls

Very large ranges materialize every value in memory and can exhaust resources. Bound the range conservatively, and prefer LATERAL JOIN + LIMIT when only a prefix is needed.
The series is inclusive at both endpoints. A daily calendar from 2025-01-01 to 2025-01-31 produces 31 rows, not 30.
Timestamp series ignore DST transitions by default and rely on the input interval. Crossing a DST boundary may produce an off-by-one hour if you are using hourly steps.
Step must be non-zero. A zero step is a runtime error, not a no-op.