range (table function)

Applies to VAST Cluster 5.5.

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

Syntax

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

Returns: one column named range, of type BIGINT for integers or TIMESTAMP for timestamps. Name it with an alias: FROM range(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_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 an empty table of sensor readings, which an example fills from range:

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_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 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}.readings"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, label VARCHAR, at TIMESTAMP)")

[]
[]

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. Each value is one row:

Integer arguments of any size give BIGINT rows. To include stop, use generate_series.

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. A month step is added to the previous value: from January 31, INTERVAL 1 MONTH gives February 28, then March 28 and April 28. A step of INTERVAL 1 YEAR from February 29, 2024 gives February 28 in the following years.

NULLs and edge cases

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

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

[]
[]
[]
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 'range(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 'range(VARCHAR)'. You might need to add explicit type casts.
[(9223372036854775806,)]

Examples

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

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

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

Timestamps 20 minutes apart within an hour, and a monthly series from January 31:

run("SELECT * FROM range(TIMESTAMP '2026-10-01 00:00', TIMESTAMP '2026-10-01 01:00', INTERVAL 20 MINUTE)")
run("SELECT * FROM range(TIMESTAMP '2026-01-31', TIMESTAMP '2026-05-01', INTERVAL 1 MONTH)")

[(datetime.datetime(2026, 10, 1, 0, 0),), (datetime.datetime(2026, 10, 1, 0, 20),), (datetime.datetime(2026, 10, 1, 0, 40),)]
[(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),)]

Fill a table with 1,000 hourly readings in one statement, then check it. INSERT ... SELECT creates the rows on the cluster, so nothing is sent from your client:

run(f"INSERT INTO {TABLE} SELECT x, 'row-' || x, TIMESTAMP '2026-10-01' + x * INTERVAL 1 HOUR FROM range(1000) t(x)")
run(f"SELECT count(*), min(id), max(id), min(at), max(at) FROM {TABLE}")

[(1000,)]
[(1000, 0, 999, datetime.datetime(2026, 10, 1, 0, 0), datetime.datetime(2026, 11, 11, 15, 0))]

An aggregate over a series, and the first rows of a series too large to list:

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

[(100000, 0, 99999)]
[(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); range replaces that scan. It produces its rows in the Query Engine, and the plan shows a RANGE step where VAST_SCAN would be. Nothing is read from storage, so a WHERE condition on the rows runs in a FILTER step above RANGE:

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

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

A LIMIT is a step of its own above RANGE:

plan("SELECT * FROM range(1000000000) t(x) LIMIT 3")

┌───────────────────────────┐
│           LIMIT           │
│    ────────────────────   │
│          Limit: 3         │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           RANGE           │
│    ────────────────────   │
│      Function: RANGE      │
│                           │
└───────────────────────────┘

Make it faster

• Put the bounds in the arguments, not in WHERE. range(5, 10) generates five rows; range(10) with WHERE x >= 5 generates ten, and the FILTER step drops five of them.
• Generate test data on the cluster. One INSERT INTO t SELECT ... FROM range(n) statement creates the rows in the Query Engine instead of sending them from your client. 100,000 rows in one statement worked.
• Use the step for regular samples. range(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. range 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 range(col) with a table's column (FROM t, range(t.n)) with The operator 'HASH_GROUP_BY' type=9 is currently not supported. UNION ALL of two series works, but the rows can come back in any order.
• No GROUP BY. It fails with The operator 'HASH_GROUP_BY' type=9 is currently not supported. An aggregate over the whole series, as in the examples, works.
• No time-zone timestamps. TIMESTAMP WITH TIME ZONE bounds fail with No function matches the given name and argument types 'range(TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE, INTERVAL)'. Cast them to TIMESTAMP. TIME values aren't accepted either.
• No CREATE TABLE ... AS SELECT. It fails with Not implemented Error: not supported; create the table, then INSERT ... SELECT.

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

InternalError: The operator 'ORDER_BY' type=1 is currently not supported for this usage.
[(4,), (3,), (2,)]
InternalError: The operator 'HASH_JOIN' type=32 is currently not supported.
InternalError: The operator 'CROSS_PRODUCT' type=33 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 'range(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),)]

Similar functions

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

[(1,), (2,), (3,)]
[(1,), (2,), (3,), (4,)]
[([1, 2, 3], [])]
[]
[(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.
• generate_series, unnest and range (list function).
• SELECT and INSERT.
• EXPLAIN: how to read a plan.