range

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 but not including stop, step apart. generate_series is the same but includes stop. In FROM, range returns rows instead of a list: see table functions.

Syntax

range(stop)
range(start, stop)
range(start, stop, step)
range(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_range. 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_range"'

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

range counts from start (0 if you leave it out) by step (1 if you leave it out), and stops before it reaches stop:

To include stop, use generate_series, or range(start, stop + 1).

Timestamps. With two timestamps and an INTERVAL, range returns the timestamps from start_ts, interval apart, before stop_ts. 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 range(NULL), range(1, NULL), range(1, 5, NULL), range(0), range(-3), range(5, 1), range(5, 5)")
run("SELECT range(1, 5, -1), range(1, 5, 0), range(0, 10, 20), range('1', '3')")
run("SELECT range(TIMESTAMP '2026-01-01', TIMESTAMP '2026-01-03', INTERVAL 0 DAY)")
run("SELECT range(1.5)")
run("SELECT range(TIMESTAMP '2026-01-01', TIMESTAMP '2026-01-03')")

[(None, None, None, [], [], [], [])]
[([], [], [0], [1, 2])]
[([],)]
InternalError: Binder Error: No function matches the given name and argument types 'range(DECIMAL(2,1))'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'range(TIMESTAMP, TIMESTAMP)'. You might need to add explicit type casts.

Examples

The pages of each job. last_page is included because the stop is last_page + 1; job 3 gets an empty list, and job 4 gets NULL:

run(f"SELECT id, range(first_page, last_page + 1) 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, and a month-by-month list from January 31:

run(f"SELECT id, range(started, started + INTERVAL 3 DAY, INTERVAL 1 DAY) FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT range(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(range(first_page, last_page + 1), 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 range() 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(range(first_page, last_page + 1), 5)")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│   list_contains("range"   │
│ (first_page, (last_page + │
│           1)), 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. range builds the whole list for every row: len(range(100000)) is 100000.

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 range takes. Cast a decimal to an integer first: range(CAST(x AS BIGINT)).
• Timestamps need an interval. range(ts1, ts2) fails; add the step, such as INTERVAL 1 DAY.
• No time-zone timestamps. range 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 range(TIMESTAMPTZ '2026-10-09 12:34:56+00', TIMESTAMPTZ '2026-10-11 12:34:56+00', INTERVAL 1 DAY)")
run("SELECT range(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)],)]

Similar functions

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

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

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.
• generate_series and table functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.