generate_series

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Build a list. Returns a list of integers, or of timestamps, from start up to and including stop, step apart. range is the same but stops before stop. In FROM, generate_series returns rows instead of a list: see table functions.

Syntax

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

Returns: BIGINT[] for integers, TIMESTAMP[] for timestamps.

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_lists_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 a table of four print jobs, each with a first and last page and a start time. In the third the last page is before the first, and the last row has no values:

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_lists_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 table's row count, which can still be 0 just after an insert."""
    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))

TABLE = f"{SCHEMA}.jobs"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, first_page BIGINT, last_page BIGINT, started TIMESTAMP)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 1, 4, TIMESTAMP '2026-03-28 12:00:00'), "
    "(2, 5, 5, TIMESTAMP '2026-01-31 00:00:00'), "
    "(3, 9, 6, TIMESTAMP '2026-10-01 08:30:00'), "
    "(4, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

generate_series counts from start (0 if you leave it out) by step (1 if you leave it out), and includes stop if a step lands on it:

generate_series(4) has five elements, not four: it starts at 0 and includes 4. To stop before stop, use range.

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

NULLs and edge cases

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

run("SELECT generate_series(NULL), generate_series(1, NULL), generate_series(1, 5, NULL), generate_series(0), "
    "generate_series(-3), generate_series(5, 5), generate_series(5, 1)")
run("SELECT generate_series(1, 5, -1), generate_series(1, 5, 0), generate_series(0, 10, 20), generate_series('1', '3')")
run("SELECT generate_series(TIMESTAMP '2026-01-01', TIMESTAMP '2026-01-03', INTERVAL 0 DAY)")
run("SELECT generate_series(1.5)")
run("SELECT generate_series(TIMESTAMP '2026-01-01', TIMESTAMP '2026-01-03')")

[(None, None, None, [0], [], [5], [])]
[([], [], [0], [1, 2, 3])]
[([],)]
InternalError: Binder Error: No function matches the given name and argument types 'generate_series(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(TIMESTAMP, TIMESTAMP)'. You might need to add explicit type casts.

Examples

The pages of each job, first and last included. Job 3 gets an empty list, and job 4 gets NULL:

run(f"SELECT id, generate_series(first_page, last_page) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [1, 2, 3, 4]), (2, [5]), (3, []), (4, None)]

The start of each of the first three days of a job (the start and two days later, both included), and a month-by-month list from January 31:

run(f"SELECT id, generate_series(started, started + INTERVAL 2 DAY, INTERVAL 1 DAY) FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT generate_series(TIMESTAMP '2026-01-31', TIMESTAMP '2026-05-31', INTERVAL 1 MONTH)")

[(1, [datetime.datetime(2026, 3, 28, 12, 0), datetime.datetime(2026, 3, 29, 12, 0), datetime.datetime(2026, 3, 30, 12, 0)]), (2, [datetime.datetime(2026, 1, 31, 0, 0), datetime.datetime(2026, 2, 1, 0, 0), datetime.datetime(2026, 2, 2, 0, 0)]), (3, [datetime.datetime(2026, 10, 1, 8, 30), datetime.datetime(2026, 10, 2, 8, 30), datetime.datetime(2026, 10, 3, 8, 30)]), (4, None)]
[([datetime.datetime(2026, 1, 31, 0, 0), datetime.datetime(2026, 2, 28, 0, 0), datetime.datetime(2026, 3, 28, 0, 0), datetime.datetime(2026, 4, 28, 0, 0), datetime.datetime(2026, 5, 28, 0, 0)],)]

Keep the jobs that print page 5:

run(f"SELECT id FROM {TABLE} WHERE list_contains(generate_series(first_page, last_page), 5) ORDER BY id LIMIT 10")

[(2,)]

How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (VAST_SCAN). VAST DataBase can evaluate a comparison of a column with a constant, but not a function of a column, so generate_series() is always computed by the Query Engine:

• In the SELECT list, the scan reads the columns and the Query Engine builds the list for each row it returns.
• In WHERE, the scan sends the columns for every row, and a FILTER step in the Query Engine builds the list and keeps the rows that match. No rows are skipped in storage.

The plan of the WHERE example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE list_contains(generate_series(first_page, last_page), 5)")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│       list_contains       │
│      (generate_series     │
│(first_page, last_page), 5)│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│         first_page        │
│         last_page         │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Test a range with comparisons, not a list. "Prints page 5" is first_page <= 5 AND last_page >= 5. Comparisons of a column with a constant are pushed down to VAST DataBase, so rows that don't match never leave storage, and the Query Engine doesn't build a list per row:

run(f"SELECT id FROM {TABLE} WHERE first_page <= 5 AND last_page >= 5 ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE first_page <= 5 AND last_page >= 5")

[(2,)]
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

• Keep lists short. generate_series builds the whole list for every row: len(generate_series(100000)) is 100001.

Limitations and errors

• Integers and timestamps only. A DECIMAL, a boolean, a date on its own or a list fails with No function matches the given name and argument types, and the error lists the forms generate_series takes. Cast a decimal to an integer first: generate_series(CAST(x AS BIGINT)).
• Timestamps need an interval. generate_series(ts1, ts2) fails; add the step, such as INTERVAL 1 DAY.
• No time-zone timestamps. generate_series on TIMESTAMPTZ values fails with Extension Autoloading Error: An error occurred while trying to automatically install the required extension 'icu':. Use TIMESTAMP values.
• A zero step returns an empty list, not an error.

run("SELECT generate_series(TIMESTAMPTZ '2026-10-09 12:34:56+00', TIMESTAMPTZ '2026-10-11 12:34:56+00', INTERVAL 1 DAY)")
run("SELECT generate_series(TIMESTAMP '2026-10-09 12:34:56', TIMESTAMP '2026-10-11 12:34:56', INTERVAL 1 DAY)")

InternalError: Extension Autoloading Error: An error occurred while trying to automatically install the required extension 'icu':
[([datetime.datetime(2026, 10, 9, 12, 34, 56), datetime.datetime(2026, 10, 10, 12, 34, 56), datetime.datetime(2026, 10, 11, 12, 34, 56)],)]

Similar functions

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

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

Clean up:

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

[]
[]

See also

• List, struct and map functions: all the list functions.
• range and table functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.