Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
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
-- 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
-- ACCESS, ASE, AURORA_MYSQL, DERBY, HANA, MEMSQL, REDSHIFT, VERTICA
/* UNSUPPORTED */
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!