Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
GENERATE_SERIES
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A nice built-in table-valued function from the PostgreSQL dialect is the GENERATE_SERIES()
function, which allows for creating a table for a range of numeric values. Many dialects have some way of generating such a table, and if not, it can be emulated using recursive SQL.
// Values from 1 to 10 Result<Record1<Integer>> r = create.selectFrom(generateSeries(1, 10)).fetch();
Dialect support
This example using jOOQ:
selectFrom(generateSeries(1, 10))
Translates to the following dialect specific expressions:
Aurora Postgres, DuckDB, Postgres, YugabyteDB
SELECT generate_series.generate_series FROM generate_series(1, 10)
BigQuery
SELECT generate_series.generate_series FROM ( SELECT null generate_series FROM UNNEST([STRUCT(1 AS dual)]) AS dual WHERE FALSE UNION ALL SELECT * FROM unnest(generate_array(1, 10)) generate_series ) generate_series
ClickHouse
SELECT generate_series.generate_series FROM ( SELECT null generate_series WHERE FALSE UNION ALL SELECT * FROM ( SELECT CAST(number AS Nullable(integer)) generate_series FROM numbers(1, (10 + 1)) ) generate_series ) generate_series
CockroachDB
SELECT generate_series.generate_series FROM generate_series(1, 10) generate_series (generate_series)
DB2
SELECT generate_series.generate_series FROM ( WITH generate_series(generate_series) AS ( SELECT 1 FROM SYSIBM.DUAL UNION ALL SELECT (generate_series + 1) FROM generate_series WHERE generate_series < 10 ) SELECT generate_series FROM generate_series ) generate_series
Exasol, Oracle
SELECT generate_series.generate_series FROM ( SELECT (level + (1 - 1)) generate_series FROM DUAL CONNECT BY level <= ((10 + 1) - 1) ) generate_series
Firebird
SELECT generate_series.generate_series FROM ( WITH RECURSIVE generate_series(generate_series) AS ( SELECT 1 FROM RDB$DATABASE UNION ALL SELECT (generate_series + 1) FROM generate_series WHERE generate_series < 10 ) SELECT generate_series FROM generate_series ) generate_series
H2
SELECT generate_series.generate_series FROM system_range(1, 10) generate_series (generate_series)
HSQLDB
SELECT generate_series.generate_series FROM ( WITH RECURSIVE generate_series(generate_series) AS ( SELECT 1 FROM (VALUES (1)) AS dual (dual) UNION ALL SELECT (generate_series + 1) FROM generate_series WHERE generate_series < 10 ) SELECT generate_series FROM generate_series ) generate_series
Informix
SELECT generate_series.generate_series FROM ( SELECT (level + (1 - 1)) generate_series FROM ( SELECT 1 AS dual FROM systables WHERE (tabid = 1) ) AS dual CONNECT BY level <= ((10 + 1) - 1) ) generate_series
MariaDB, MySQL, SQLite, Trino
SELECT generate_series.generate_series FROM ( WITH RECURSIVE generate_series(generate_series) AS ( SELECT 1 UNION ALL SELECT (generate_series + 1) FROM generate_series WHERE generate_series < 10 ) SELECT generate_series FROM generate_series ) generate_series
Snowflake
SELECT generate_series.generate_series FROM ( SELECT ((seq4() + 1) + (1 - 1)) generate_series FROM TABLE(generator(rowcount => (10 - (1 - 1)))) ) generate_series (generate_series)
SQLDataWarehouse
WITH generate_series(generate_series) AS ( SELECT 1 UNION ALL SELECT (generate_series + 1) FROM generate_series WHERE generate_series < 10 ) SELECT generate_series.generate_series FROM ( SELECT generate_series FROM generate_series ) generate_series
SQLServer
SELECT generate_series.generate_series FROM ( SELECT * FROM generate_series(1, 10) ) generate_series (generate_series)
Sybase
SELECT generate_series.generate_series FROM ( WITH RECURSIVE generate_series(generate_series) AS ( SELECT 1 FROM SYS.DUMMY UNION ALL SELECT (generate_series + 1) FROM generate_series WHERE generate_series < 10 ) SELECT generate_series FROM generate_series ) generate_series
Teradata
WITH RECURSIVE generate_series(generate_series) AS ( SELECT 1 FROM ( SELECT 1 AS "dual" ) AS "dual" UNION ALL SELECT (generate_series + 1) FROM generate_series WHERE generate_series < 10 ) SELECT generate_series.generate_series FROM ( SELECT generate_series FROM generate_series ) generate_series
ASE, Access, Aurora MySQL, Derby, Hana, MemSQL, Redshift, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!