generate_series (table function)

Applies to VAST Cluster 5.5.

Functions › Table functions › Series of numbers and timestamps. In FROM, generate_series returns rows: integers, or timestamps, from start to stop included, step apart. range is the same but stops before stop. In the SELECT list, generate_series returns the same values as one list instead: see generate_series (list function).

Syntax

generate_series(stop)
generate_series(start, stop)
generate_series(start, stop, step)
generate_series(start_ts, stop_ts, interval)

Returns: one column named generate_series, of type BIGINT for integers or TIMESTAMP for timestamps. Name it with an alias: FROM generate_series(3) t(x).

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_table_functions_generate_series. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the run() and plan() helpers, and creates an empty table of time slots, which an example fills from generate_series:

import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_table_functions_generate_series"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the estimated row count."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line).replace(BUCKET, "<bucket>"))

TABLE = f"{SCHEMA}.slots"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, at TIMESTAMP)")

[]
[]

What it does

generate_series counts from start (0 if you leave it out) by step (1 if you leave it out), up to and including stop when a step lands on it. Each value is one row:

Integer arguments of any size give BIGINT rows. To stop before stop, use range.

Timestamps. With two timestamps and an INTERVAL, generate_series returns the timestamps from start_ts, interval apart, up to and including stop_ts. A DATE is converted to a TIMESTAMP at midnight. A month step is added to the previous value: from January 31, INTERVAL 1 MONTH gives February 28, then March 28 and April 28.

NULLs and edge cases

The same cases, as the cluster returns them. They use the helpers from Set up the examples:

run("SELECT * FROM generate_series(1, NULL)")
run("SELECT * FROM generate_series(0)")
run("SELECT * FROM generate_series(2, 2)")
run("SELECT * FROM generate_series(-2)")
run("SELECT * FROM generate_series(5, 2)")
run("SELECT * FROM generate_series(0, 10, -1)")
run("SELECT * FROM generate_series(0, 10, 0)")
run("SELECT * FROM generate_series(1, 3, 0.5)")
run("SELECT * FROM generate_series('3')")
run("SELECT * FROM generate_series(9223372036854775807, 9223372036854775807)")

[]
[(0,)]
[(2,)]
OSError: start is bigger than end, but increment is positive: cannot generate infinite series
OSError: start is bigger than end, but increment is positive: cannot generate infinite series
OSError: start is smaller than end, but increment is negative: cannot generate infinite series
OSError: interval cannot be 0!
InternalError: Binder Error: No function matches the given name and argument types 'generate_series(INTEGER, INTEGER, DECIMAL(2,1))'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'generate_series(VARCHAR)'. You might need to add explicit type casts.
[(9223372036854775807,)]

Examples

Name the column with an alias, and compute other columns from it:

run("SELECT x, x * x, 'id-' || x FROM generate_series(1, 4) t(x)")

[(1, 1, 'id-1'), (2, 4, 'id-2'), (3, 9, 'id-3'), (4, 16, 'id-4')]

Every 20 minutes of an hour, both ends included, and every day of a short period:

run("SELECT * FROM generate_series(TIMESTAMP '2026-10-01 00:00', TIMESTAMP '2026-10-01 01:00', INTERVAL 20 MINUTE)")
run("SELECT * FROM generate_series(DATE '2026-10-01', DATE '2026-10-04', INTERVAL 1 DAY)")

[(datetime.datetime(2026, 10, 1, 0, 0),), (datetime.datetime(2026, 10, 1, 0, 20),), (datetime.datetime(2026, 10, 1, 0, 40),), (datetime.datetime(2026, 10, 1, 1, 0),)]
[(datetime.datetime(2026, 10, 1, 0, 0),), (datetime.datetime(2026, 10, 2, 0, 0),), (datetime.datetime(2026, 10, 3, 0, 0),), (datetime.datetime(2026, 10, 4, 0, 0),)]

Fill a table with one slot per hour of a day, both midnights included (25 rows), numbering them with row_number(). INSERT ... SELECT creates the rows on the cluster:

run(f"INSERT INTO {TABLE} SELECT row_number() OVER (), ts "
    "FROM generate_series(TIMESTAMP '2026-10-01', TIMESTAMP '2026-10-02', INTERVAL 1 HOUR) t(ts)")
run(f"SELECT count(*), min(id), max(id), min(at), max(at) FROM {TABLE}")

[(25,)]
[(25, 1, 25, datetime.datetime(2026, 10, 1, 0, 0), datetime.datetime(2026, 10, 2, 0, 0))]

An aggregate over a series, which has 100,001 rows because stop is included, and the first rows of a series too large to list:

run("SELECT count(*), min(x), max(x) FROM generate_series(100000) t(x)")
run("SELECT * FROM generate_series(1000000000) t(x) LIMIT 3")

[(100001, 0, 100000)]
[(0,), (1,), (2,)]

How it runs in VAST

The Query Engine runs on the cluster's CNodes. A query on a table starts with a scan in VAST DataBase (VAST_SCAN); generate_series replaces that scan. It produces its rows in the Query Engine, and the plan shows a GENERATE_SERIES step where VAST_SCAN would be. Nothing is read from storage, so a WHERE condition on the rows runs in a FILTER step above it:

plan("SELECT x FROM generate_series(10) t(x) WHERE x > 5")

┌───────────────────────────┐
│           FILTER          │
│    ────────────────────   │
│          (x > 5)          │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      GENERATE_SERIES      │
│    ────────────────────   │
│         Function:         │
│      GENERATE_SERIES      │
│                           │
└───────────────────────────┘

Make it faster

• Put the bounds in the arguments, not in WHERE. generate_series(5, 10) generates six rows; generate_series(10) with WHERE x >= 5 generates eleven, and the FILTER step drops five of them.
• Generate test data on the cluster. One INSERT INTO t SELECT ... FROM generate_series(...) statement creates the rows in the Query Engine instead of sending them from your client.
• Use the step for regular samples. generate_series(0, 1000, 10) gives every tenth value directly, instead of WHERE x % 10 = 0 on a longer series.

Limitations and errors

• ORDER BY needs a LIMIT. Without one it fails with The operator 'ORDER_BY' type=1 is currently not supported for this usage.
• One source per query. generate_series can't be joined with a table or with another table function: a join fails with The operator 'HASH_JOIN' type=32 is currently not supported., two table functions in FROM with The operator 'CROSS_PRODUCT' type=33 is currently not supported., and generate_series(col) with a table's column with The operator 'HASH_GROUP_BY' type=9 is currently not supported. UNION ALL of two series works.
• No GROUP BY. It fails with The operator 'HASH_GROUP_BY' type=9 is currently not supported. An aggregate over the whole series works.
• No time-zone timestamps. TIMESTAMP WITH TIME ZONE bounds fail with No function matches the given name and argument types 'generate_series(TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE, INTERVAL)'. Use TIMESTAMP values. TIME values aren't accepted either.
• Timestamps need an interval. generate_series(ts1, ts2) fails; add the step, such as INTERVAL 1 DAY.

run("SELECT x FROM generate_series(5) t(x) ORDER BY x DESC")
run("SELECT x FROM generate_series(5) t(x) ORDER BY x DESC LIMIT 3")
run(f"SELECT id, x FROM {TABLE} JOIN generate_series(3) r(x) ON id = x")
run("SELECT x % 3 AS k, count(*) FROM generate_series(10) t(x) GROUP BY k ORDER BY k LIMIT 20")
run("SELECT * FROM generate_series(TIMESTAMPTZ '2026-10-09 12:34:56+00', TIMESTAMPTZ '2026-10-11 12:34:56+00', INTERVAL 1 DAY)")
run("SELECT * FROM generate_series(TIMESTAMP '2026-10-09 12:34:56', TIMESTAMP '2026-10-11 12:34:56', INTERVAL 1 DAY)")
run("SELECT * FROM generate_series(TIMESTAMP '2026-10-01', TIMESTAMP '2026-10-04')")

InternalError: The operator 'ORDER_BY' type=1 is currently not supported for this usage.
[(5,), (4,), (3,)]
InternalError: The operator 'HASH_JOIN' type=32 is currently not supported.
InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.
InternalError: Binder Error: No function matches the given name and argument types 'generate_series(TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE, INTERVAL)'. You might need to add explicit type casts.
[(datetime.datetime(2026, 10, 9, 12, 34, 56),), (datetime.datetime(2026, 10, 10, 12, 34, 56),), (datetime.datetime(2026, 10, 11, 12, 34, 56),)]
InternalError: Binder Error: No function matches the given name and argument types 'generate_series(TIMESTAMP, TIMESTAMP)'. You might need to add explicit type casts.

Similar functions

run("SELECT * FROM generate_series(1, 4)")
run("SELECT * FROM range(1, 4)")
run("SELECT generate_series(1, 4), generate_series(0)")

[(1,), (2,), (3,), (4,)]
[(1,), (2,), (3,)]
[([1, 2, 3, 4], [0])]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• Table functions: all the table functions.
• range, unnest and generate_series (list function).
• SELECT and INSERT.
• EXPLAIN: how to read a plan.