repeat (table function)

Applies to VAST Cluster 5.5.

Functions › Table functions › Repeated rows. In FROM, repeat(value, n) returns n rows, each holding value. In the SELECT list, repeat is a different function that repeats text or bytes inside one value: see repeat (string function) and repeat for BLOB values.

Syntax

repeat(value, n)

Returns: one column of the value's type. The column is named after the value itself, so give it an alias: FROM repeat('row', 2) t(v).

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_repeat. 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 tasks, which an example fills from repeat:

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_repeat"'

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}.tasks"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, status VARCHAR)")

[]
[]

What it does

repeat(value, n) returns n rows with the same value in each:

The value can be of any type, a list or a struct included. It's computed once: repeat(gen_random_uuid(), 3) returns the same UUID in all three rows.

NULLs and edge cases

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

run("SELECT * FROM repeat(NULL, 2)")
run("SELECT * FROM repeat('x', 0)")
run("SELECT * FROM repeat('x', -1)")
run("SELECT * FROM repeat('x', NULL)")
run("SELECT * FROM repeat('x', 2.5)")
run("SELECT * FROM repeat('x')")

[(None,), (None,)]
[]
InternalError: Binder Error: Repeat second parameter cannot be be less than 0
InternalError: Binder Error: Repeat second parameter cannot be NULL
InternalError: Binder Error: No function matches the given name and argument types 'repeat(VARCHAR, DECIMAL(2,1))'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'repeat(VARCHAR)'. You might need to add explicit type casts.

Examples

Name the column with an alias, and repeat a list, a struct and a date:

run("SELECT v FROM repeat('row', 3) t(v)")
run("SELECT * FROM repeat([1, 2], 2)")
run("SELECT * FROM repeat({'a': 1}, 2)")
run("SELECT * FROM repeat(DATE '2026-10-16', 2)")

[('row',), ('row',), ('row',)]
[([1, 2],), ([1, 2],)]
[({'a': 1},), ({'a': 1},)]
[(datetime.date(2026, 10, 16),), (datetime.date(2026, 10, 16),)]

Add three tasks with the same status to a table in one statement, then read them. The tasks have no id, because the statement names only status:

run(f"INSERT INTO {TABLE} (status) SELECT * FROM repeat('todo', 3)")
run(f"SELECT id, status FROM {TABLE} LIMIT 10")

[(3,)]
[(None, 'todo'), (None, 'todo'), (None, 'todo')]

Count a large number of repeated rows, and read the first rows of a larger one:

run("SELECT count(*) FROM repeat('x', 100000)")
run("SELECT * FROM repeat('x', 1000000000) LIMIT 3")

[(100000,)]
[('x',), ('x',), ('x',)]

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); repeat replaces that scan. It produces its rows in the Query Engine, and the plan shows a REPEAT step where VAST_SCAN would be. Nothing is read from storage, so a WHERE condition on the rows runs in the Query Engine:

plan("SELECT * FROM repeat('x', 3)")
plan("SELECT v FROM repeat(5, 3) t(v) WHERE v > 1")

┌───────────────────────────┐
│          REPEAT           │
│    ────────────────────   │
│      Function: REPEAT     │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│           FILTER          │
│    ────────────────────   │
│          (v > 1)          │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│          REPEAT           │
│    ────────────────────   │
│      Function: REPEAT     │
│                           │
└───────────────────────────┘

Make it faster

• Generate rows on the cluster. INSERT INTO t (col) SELECT * FROM repeat(value, n) creates the rows in the Query Engine instead of sending them from your client.
• Use LIMIT to sample. repeat('x', 1000000000) LIMIT 3 returns its first three rows.

Limitations and errors

• One source per query. Like the other table functions, repeat can't be joined with a table or with another table function (table functions).
• No aggregate over repeat in a scalar subquery. SELECT repeat('ab', 3), (SELECT count(*) FROM repeat('ab', 3)) fails with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. Run the aggregate as a query of its own, as in the examples.
• Several columns per row. repeat returns one column. For a row of several values, use repeat_row.

run("SELECT repeat('ab', 3), (SELECT count(*) FROM repeat('ab', 3))")
run("SELECT * FROM repeat('x', 2) a(x), repeat('y', 2) b(y)")

InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.
InternalError: The operator 'CROSS_PRODUCT' type=33 is currently not supported.

Similar functions

run("SELECT * FROM repeat('ab', 3)")
run("SELECT * FROM repeat_row('ab', 1, num_rows := 3)")
run("SELECT * FROM range(3)")
run("SELECT repeat('ab', 3)")

[('ab',), ('ab',), ('ab',)]
[('ab', 1), ('ab', 1), ('ab', 1)]
[(0,), (1,), (2,)]
[('ababab',)]

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.
• repeat_row, range and unnest.
• INSERT and SELECT.
• EXPLAIN: how to read a plan.