Acquire / generate · All operations

Generate a series of numbers

A variant of Generate rows

Emit one row per integer from a start to an end by a step.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Emit the integers from a start to an end by a step

SQL
CREATE TEMP TABLE result AS
SELECT (SELECT list(i ORDER BY i) FROM generate_series(1, 10, 3) t(i)) AS up,
       (SELECT list(i ORDER BY i DESC) FROM generate_series(10, 1, -3) t(i)) AS down,
       (SELECT count(*) FROM generate_series(5, 1)) AS backwards,
       (SELECT list(i ORDER BY i) FROM range(1, 10, 3) t(i)) AS end_excluded;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT up = [1, 4, 7, 10] AND down = [10, 7, 4, 1] AND backwards = 0 AND end_excluded = [1, 4, 7] FROM result)) THEN 'as expected' ELSE error('the series did not run 1, 4, 7, 10 up and 10, 7, 4, 1 down, or an end before the start did not give no rows') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the series did not run 1, 4, 7, 10 up and 10, 7, 4, 1 down, or an end before the start did not give no rows”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache DataFusion (1)
Bruin (1)
dbt-utils (1)
  • generate_seriesread 2026-09-27

    Generate a 1-indexed row set of integers up to an upper bound

DuckDB (2)
  • generate_seriesread 2026-09-27

    generate_series table function

  • rangeread 2026-09-27

    range table function

EasyMorph (1)
  • Sequenceread 2026-09-27

    Generate the integers 1 to N as rows

Ibis (1)
  • IntegerRangeread 2026-09-27

    Generate an array of integers from start to stop by a step

Polars (1)

Background

In PostgreSQL, generate_series produces a series of values from a start to a stop with a step size, and the step defaults to 1.[1] In DuckDB, generate_series includes its stop value while range leaves it out, so range(2, 5) gives the rows 2, 3 and 4.[2]

When to use it

EasyMorph's Sequence action creates a table of integers starting from 1, which can be used later for creating loops.[3] The dbt-utils package's generate_series macro is a cross-database way to generate an arbitrarily long list of numbers, as a 1-indexed result set up to a given maximum.[4] Use a series of numbers as a scaffold: one row per position, per attempt or per bucket, from which the other values are then computed. (Meridian's view)

In PostgreSQL, generate_series returns zero rows when the step is positive and the start is greater than the stop, or when the step is negative and the start is less than the stop.[1] In PostgreSQL, generate_series also returns zero rows if any input is NULL, and a step of zero is an error.[1] Count the rows a series gives before you rely on it, and read an empty series as a sign that the start and the stop are the wrong way round. (Meridian's view) In DuckDB, range and generate_series start from 0 when no start is given, and step by 1 when no step is given.[5] Bruin's generate_series macro gives a single column named generated_number, with values from 1 through its upper bound.[6] Write the start out rather than leaning on a default, so that a series reads the same in every tool. (Meridian's view)

The method

E. W. Dijkstra preferred writing the run 2, 3, ..., 12 as 2 ≤ i < 13, with the lower bound included and the upper bound excluded.[7] When one bound is included and the other excluded, the difference between the bounds equals the length of the run, and two adjacent runs meet where the upper bound of one equals the lower bound of the other.[7]

In DuckDB, generate_series(2, 5, 3) gives 2 and 5, while range(2, 5, 3) gives only 2.[5] In PostgreSQL, generate_series takes integer, bigint and numeric arguments, so generate_series(1.1, 4, 1.3) gives 1.1, 2.4 and 3.7.[1]

Compared with the alternatives

In PostgreSQL, WITH ORDINALITY after a function in the FROM clause appends a bigint column that starts from 1 and increments by 1 for each row of the function's output.[1] When the numbers only need to label rows that already exist, number those rows instead of generating a series and joining it on. (Meridian's view)

Sources

  1. [1]PostgreSQL: Documentation: 18: 9.26. Set Returning Functionsdocumentation
  2. [2]Utility Functions – DuckDBdocumentation
  3. [3]SEQUENCE [EasyMorph Help]documentation
  4. [4]dbt-utils README.mddocumentation
  5. [5]List Functions – DuckDBdocumentation
  6. [6]Macros | Bruin CLIdocumentation
  7. [7]E.W. Dijkstra Archive: Why numbering should start at zero (EWD 831)secondary source