Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
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)
Databricks
SELECT generate_series.generate_series FROM explode(sequence(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.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!