GENERATE_SERIESFunctions
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

Show:
Database System Support Status Since Version Notes
MySQL ✗ Not Supported — Not supported. Workaround: recursive CTE (limited to @@cte_max_recursion_depth, default 1000).
PostgreSQL ✓ Supported 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 ✓ Supported 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 ✓ Supported 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: Since 8.0 SQL Server: Since 2022 Oracle: Not supported SQLite: Since 3.31.0

Per-Database Syntax & Notes

MySQL

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

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

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

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

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

SQLite

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);