Skip to content

Table functions

Changelog (last updated v2.2)
v2.2: GENERATE_SERIES is end-inclusive

GENERATE_SERIES(start, end) now includes end in its output, matching PostgreSQL and DuckDB.

Previously, GENERATE_SERIES was end-exclusive — equivalent to the new RANGE function.

Upgrade steps:

  • Either: migrate queries from GENERATE_SERIES to RANGE to keep end-exclusive behaviour.
  • Or: leave queries as-is and accept the new inclusive semantics — most callers will want this.
  • Escape hatch: set XTDB_GENERATE_SERIES_END_EXCLUSIVE=true on the node to revert GENERATE_SERIES to its pre-2.2 (end-exclusive) behaviour while you migrate. RANGE is unaffected by this flag.

Functions usable in the FROM clause of a query to produce a relation.

See table reference in the query grammar for how these slot into the wider FROM clause alongside VALUES, UNNEST, sub-queries, and so on.

Series-generating functions

Section titled “Series-generating functions”
RANGE(start, end [, stride]) (v2.2+)
generates a series of values from start (inclusive) to end (exclusive), with the given stride.
  • Works over integers (stride defaults to 1) and temporal types (stride is an INTERVAL).
  • Time-zone-aware: if start/end carry a time-zone, the series honours any daylight-savings transitions between them — note the difference between adding INTERVAL 'P1D' (1 calendar day) and INTERVAL 'PT24H' (24 hours) across a DST boundary.
  • XTDB’s time-zone handling is an extension to PostgreSQL’s generate_series.
FROM RANGE(DATE '2020-01-01', DATE '2020-01-04', INTERVAL '1' DAY)
-- yields: [DATE '2020-01-01', DATE '2020-01-02', DATE '2020-01-03']


FROM RANGE(TIMESTAMP '2020-01-01T00:00:00Z',
           TIMESTAMP '2020-01-01T01:00:00Z',
           INTERVAL 'PT15M')
-- yields: [00:00Z, 00:15Z, 00:30Z, 00:45Z]


FROM RANGE(TIMESTAMP '2020-03-29T00:00:00Z[Europe/London]',
           TIMESTAMP '2020-03-31T00:00:00+01:00[Europe/London]',
           INTERVAL 'P1D')
-- yields: [2020-03-29T00:00:00Z[Europe/London], 2020-03-30T00:00:00+01:00[Europe/London]]
GENERATE_SERIES(start, end [, stride])
like RANGE, but with an inclusive end bound — matching PostgreSQL’s generate_series semantics. 
FROM GENERATE_SERIES(1, 4)
-- yields: [1, 2, 3, 4]


FROM GENERATE_SERIES(DATE '2020-01-01', DATE '2020-01-04', INTERVAL '1' DAY)
-- yields: [DATE '2020-01-01', DATE '2020-01-02', DATE '2020-01-03', DATE '2020-01-04']

Scalar functions in FROM

Section titled “Scalar functions in FROM”

Any scalar function call (v2.2+) may be used as a table source, in which case its result is wrapped as a single-row table whose default column name is the function name.

  • WITH ORDINALITY is supported.
  • Aggregate functions are rejected with a clear error.
FROM STRING_TO_ARRAY('a,b,c', ',') AS parts
-- single-row table with column `parts` = ['a', 'b', 'c']

Ordinality

Section titled “Ordinality”

Any table function may be suffixed with WITH ORDINALITY to append a 1-based row-number column to the output:

FROM GENERATE_SERIES(1, 4) WITH ORDINALITY AS t(n, idx)
-- n | idx
-- --+----
-- 1 |  1
-- 2 |  2
-- 3 |  3