GENERATE_SERIES

Functions
Buy Me A Coffee

Generate a set of integer, numeric, or timestamp values as a virtual table row source.

Generates a series of values from start to end with an optional step, available in PostgreSQL and SQLite (3.31.0+). Essential for generating date sequences, test data, and calendar tables without creating physical tables.

Compatibility

Filter by Database
SQL GENERATE_SERIES Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Not Supported - Not supported. Workaround: recursive CTE (limited to @@cte_max_recursion_depth, default 1000).
PostgreSQL Native 8.0 generate_series(start, stop[, step]) for integers/bigints/numerics. Timestamp support added in 8.4. Negative step works. Returns empty set if start > stop (with positive step).
SQL Server Native 2022 GENERATE_SERIES(start, stop[, step]) added in SQL Server 2022. Requires database compatibility level 160. Supports int, bigint, decimal, numeric. No timestamp/date support.
Oracle Not Supported - Not supported. Workaround: CONNECT BY LEVEL for integers; recursive CTE for other types.
SQLite Native 3.31.0 Built-in table-valued function since 3.31.0, but may require explicit loading in some environments and is not enabled by default.

Details

PostgreSQL's timestamp/interval support is the killer feature — filling a date spine for reporting, finding calendar gaps, or generating billing periods is trivial. SQL Server 2022 added a limited version (integers/decimals only, requires compatibility level 160).

Standard Syntax

SELECT * FROM generate_series(1, 10) AS n; -- Calendar of months: SELECT generate_series( '2024-01-01'::date, '2024-12-01', '1 month' )::date AS month;

Version Support

MySQL: Not supported PostgreSQL: Native since 8.0 SQL Server: Native since 2022 Oracle: Not supported SQLite: Native since 3.31.0

Per-Database Syntax & Notes

MySQL Alternative syntax

Not supported. Workaround: recursive CTE (limited to @@cte_max_recursion_depth, default 1000).

WITH RECURSIVE n AS ( SELECT 1 AS val UNION ALL SELECT val + 1 FROM n WHERE val < 10 ) SELECT val FROM n;

PostgreSQL Native syntax

generate_series(start, stop[, step]) for integers/bigints/numerics. Timestamp support added in 8.4. Negative step works. Returns empty set if start > stop (with positive step).

SELECT generate_series(1, 10); SELECT generate_series(1, 10, 2); -- odd numbers: 1,3,5,7,9 SELECT generate_series( '2024-01-01'::timestamptz, '2024-12-31', '1 month' ) AS month;

SQL Server Native syntax

GENERATE_SERIES(start, stop[, step]) added in SQL Server 2022. Requires database compatibility level 160. Supports int, bigint, decimal, numeric. No timestamp/date support.

SELECT value FROM GENERATE_SERIES(1, 10); SELECT value FROM GENERATE_SERIES(1, 10, 2); -- Requires: ALTER DATABASE db SET COMPATIBILITY_LEVEL = 160;

Oracle Alternative syntax

Not supported. Workaround: CONNECT BY LEVEL for integers; recursive CTE for other types.

SELECT LEVEL AS n FROM DUAL CONNECT BY LEVEL <= 10;

SQLite Native syntax

Built-in table-valued function since 3.31.0, but may require explicit loading in some environments and is not enabled by default.

SELECT value FROM generate_series(1, 10);