Functions/Lists & arrays/generate_series
Lists & arrays Scalar Table

generate_series()

Creates a list of values between start and stop - the stop parameter is inclusive.

Arguments & returns

10 overloads

generate_series(start)

Scalar
Overload link start Returns
#01 BIGINT BIGINT[]

generate_series(start, stop)

Scalar
Overload link startstop Returns
#02 BIGINTBIGINT BIGINT[]

generate_series(start, stop, step)

Scalar
Overload link startstopstep Returns
#03 BIGINTBIGINTBIGINT BIGINT[]
#04 TIMESTAMP WITH TIME ZONETIMESTAMP WITH TIME ZONEINTERVAL TIMESTAMP WITH TIME ZONE[]
#05 TIMESTAMPTIMESTAMPINTERVAL TIMESTAMP[]

generate_series(col0)

Table
Overload link col0 Returns
#06 BIGINT TABLE

generate_series(col0, col1)

Table
Overload link col0col1 Returns
#07 BIGINTBIGINT TABLE

generate_series(col0, col1, col2)

Table
Overload link col0col1col2 Returns
#08 BIGINTBIGINTBIGINT TABLE
#09 TIMESTAMP WITH TIME ZONETIMESTAMP WITH TIME ZONEINTERVAL TABLE
#10 TIMESTAMPTIMESTAMPINTERVAL TABLE

Example

generate_series example
SQL
Haybarn WASM 1.5.5-rc3 · In your browser

Engine example · Run to view results.

In other engines

BigQuerygenerate_array()

GENERATE_ARRAY(1, 4)
In DuckDB
GENERATE_SERIES(1, 4)

SQLGlot example ↗

Dremioarray_generate_range()

ARRAY_GENERATE_RANGE(1, 4)
In DuckDB
GENERATE_SERIES(1, 4)

SQLGlot example ↗

Used in larger rewrites 2

These examples use generate_series as one part of a larger SQL translation.

PostgreSQL

WITH dates AS (SELECT GENERATE_SERIES('2020-01-01'::DATE, '2024-01-01'::DATE, '1 day'::INTERVAL) AS date), date_table AS (SELECT DISTINCT DATE_TRUNC('MONTH', date) AS date FROM dates) SELECT * FROM date_table
In DuckDB
WITH dates AS (SELECT UNNEST(GENERATE_SERIES(CAST('2020-01-01' AS DATE), CAST('2024-01-01' AS DATE), CAST('1 day' AS INTERVAL))) AS date), date_table AS (SELECT DISTINCT DATE_TRUNC('MONTH', date) AS date FROM dates) SELECT * FROM date_table

SQLGlot example ↗

Snowflakearray_except()

SELECT ARRAY_EXCEPT([1, 2, 3], [2])
In DuckDB
SELECT CASE WHEN [1, 2, 3] IS NULL OR [2] IS NULL THEN NULL ELSE LIST_TRANSFORM(LIST_FILTER(LIST_ZIP([1, 2, 3], GENERATE_SERIES(1, LENGTH([1, 2, 3]))), pair -> (LENGTH(LIST_FILTER([1, 2, 3][1:pair[2]], e -> e IS NOT DISTINCT FROM pair[1])) > LENGTH(LIST_FILTER([2], e -> e IS NOT DISTINCT FROM pair[1])))), pair -> pair[1]) END

SQLGlot example ↗