--- title: "repeat (table function)" slug: "dev-database-reference-sql-functions-table-functions-repeat" updated: 2026-10-30T18:18:46Z published: 2026-10-30T18:18:46Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-table-functions-repeat" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # repeat (table function) *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Table functions](/develop/docs/dev-database-reference-sql-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)](/develop/docs/dev-database-reference-sql-functions-string-repeat) and [repeat for BLOB values](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-repeat). ## Syntax ``` repeat(value, n) ``` | Argument | Type | Description | | --- | --- | --- | | `value` | Any type: numbers, text, dates, lists, structs, `BLOB`s | The value each row holds. | | `n` | `BIGINT` (any integer) | The number of rows: 0 or more. | **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)](/develop/docs/dev-database-integrations-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](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), 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, "") 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, "")) 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: | Call | Rows | | --- | --- | | `repeat('row', 3)` | `'row'`, `'row'`, `'row'` | | `repeat(42, 2)` | `42`, `42` | | `repeat([1, 2], 2)` | `[1, 2]`, `[1, 2]` | | `repeat('x', 0)` | no rows | 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 | Input | Result | | --- | --- | | A NULL value, `repeat(NULL, 2)` | 2 NULL rows | | `n` of 0 | No rows | | A negative `n` | Error: `Binder Error: Repeat second parameter cannot be be less than 0` | | A NULL `n` | Error: `Binder Error: Repeat second parameter cannot be NULL` | | A decimal or a string `n`, such as `2.5` or `'2'` | Error: `No function matches the given name and argument types`, for example `'repeat(VARCHAR, DECIMAL(2,1))'` | | No `n`, or a third argument | Error: `No function matches the given name and argument types` | The same cases, as the cluster returns them. They use the helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-table-functions-repeat#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](/develop/docs/dev-database-reference-sql-functions-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](/develop/docs/dev-database-reference-sql-functions-table-functions-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 | Function | Returns | | --- | --- | | `repeat(value, n)` in `FROM` | `n` rows of one column holding `value` | | [repeat_row](/develop/docs/dev-database-reference-sql-functions-table-functions-repeat-row)`(a, b, ..., num_rows := n)` | `n` rows of several columns | | [range](/develop/docs/dev-database-reference-sql-functions-table-functions-range)`(n)` in `FROM` | `n` rows of different numbers, `0` to `n - 1` | | [repeat (string function)](/develop/docs/dev-database-reference-sql-functions-string-repeat), in the `SELECT` list | one string, the text repeated `n` times | ``` 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](/develop/docs/dev-database-reference-sql-functions-table-functions): all the table functions. - [repeat_row](/develop/docs/dev-database-reference-sql-functions-table-functions-repeat-row), [range](/develop/docs/dev-database-reference-sql-functions-table-functions-range) and [unnest](/develop/docs/dev-database-reference-sql-functions-table-functions-unnest). - [INSERT](/develop/docs/dev-database-reference-sql-insert) and [SELECT](/develop/docs/dev-database-reference-sql-select). - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.