--- title: "nextval" slug: "dev-database-reference-sql-functions-utility-nextval" updated: 2026-10-30T18:18:46Z published: 2026-10-30T18:18:46Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-utility-nextval" --- > ## 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. # nextval *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Session and utility functions](/develop/docs/dev-database-reference-sql-functions-utility) › Session and diagnostics.* `nextval(name)` returns the next value of a sequence and `currval(name)` the value `nextval` last returned. The VAST Query Engine doesn't support `CREATE SEQUENCE`, so there's no sequence for them to read: both fail unless the name is `NULL`. To number rows, use [row_number](/develop/docs/dev-database-reference-sql-functions-aggregate-row-number); to generate unique keys, use [gen_random_uuid](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-gen-random-uuid). ## Syntax ``` nextval(sequence_name) currval(sequence_name) ``` | Argument | Type | Description | | --- | --- | --- | | `sequence_name` | `VARCHAR` | The name of a sequence. | **Returns:** `BIGINT`. ## 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_string_nextval`. 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 a table of three orders. The last row has no item: ``` 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_string_nextval"' 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 table's row count, which can still be 0 just after an insert), with your database name shown as .""" 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}.orders" TICKETS = f"{SCHEMA}.tickets" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, item VARCHAR)") run(f"INSERT INTO {TABLE} VALUES (1, 'disk'), (2, 'cable'), (3, NULL)") ``` ``` [] [] [(3,)] ``` ## What it does `nextval` and `currval` look up a sequence object by name. A sequence is created with `CREATE SEQUENCE`, which the VAST Query Engine doesn't support: it fails with `The operator 'CREATE_SEQUENCE' type=52 is currently not supported.` No sequence can exist, so `nextval` and `currval` fail with `Sequence with name ... does not exist!` for any name: ``` run(f"CREATE SEQUENCE {SCHEMA}.docs_seq START 1") run("SELECT nextval('docs_seq')") run("SELECT currval('docs_seq')") ``` ``` InternalError: The operator 'CREATE_SEQUENCE' type=52 is currently not supported. InternalError: Catalog Error: Sequence with name docs_seq does not exist! InternalError: Catalog Error: Sequence with name docs_seq does not exist! ``` ## NULLs and edge cases | Input | Result | | --- | --- | | `nextval(NULL)` | `NULL` | | `nextval('docs_seq')`, any name | Error: `Sequence with name docs_seq does not exist!` | | `nextval('docs_seq')` on every row of a table | The same error; no rows are returned | | `CREATE TABLE ... (id BIGINT DEFAULT nextval('docs_seq'), ...)` | The same error; the table isn't created | The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-utility-nextval#set-up-the-examples): ``` run("SELECT nextval(NULL)") run(f"SELECT id, nextval('docs_seq') FROM {TABLE} ORDER BY id LIMIT 10") run(f"CREATE TABLE {SCHEMA}.seq_t (id BIGINT DEFAULT nextval('docs_seq'), v VARCHAR)") ``` ``` [(None,)] InternalError: Catalog Error: Sequence with name docs_seq does not exist! InternalError: Catalog Error: Sequence with name docs_seq does not exist! ``` ## Examples Number the rows of a result with `row_number() OVER ()`. Sort them in a subquery first, with `ORDER BY ... LIMIT`, because `ORDER BY` inside `OVER` isn't available. Add an offset to start the numbers where you want: ``` run(f"SELECT id, item, 1000 + row_number() OVER () AS order_no " f"FROM (SELECT id, item FROM {TABLE} ORDER BY id LIMIT 100)") ``` ``` [(1, 'disk', 1001), (2, 'cable', 1002), (3, None, 1003)] ``` Give each row a unique key when you write it: store `gen_random_uuid()` in a `UUID` column. Each call returns a new value, so no two rows share a key: ``` run(f"CREATE TABLE IF NOT EXISTS {TICKETS} (ticket UUID, item VARCHAR)") run(f"INSERT INTO {TICKETS} SELECT gen_random_uuid(), item FROM {TABLE}") run(f"SELECT count(*), count(ticket) FROM {TICKETS}") run("SELECT typeof(gen_random_uuid()), gen_random_uuid() = gen_random_uuid()") ``` ``` [] [(3,)] [(3, 3)] [('UUID', False)] ``` ## How it runs in VAST The Query Engine runs on the cluster's CNodes. It looks a sequence name up in its catalog when it plans the query, before any scan starts in VAST DataBase. The lookup fails, so the query returns the `Catalog Error` and reads no rows, whether `nextval` is called on its own, in the `SELECT` list of a table query, or in a column default. A `NULL` name has nothing to look up, and the result is `NULL`. `row_number() OVER ()` and `gen_random_uuid()` are computed by the Query Engine for each row the scan returns. The plan of the numbering example has no `FILTER` step; the numbering runs in the Query Engine above the sorted subquery: ``` plan(f"SELECT id, row_number() OVER () FROM (SELECT id FROM {TABLE} ORDER BY id LIMIT 100)") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #0 │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ STREAMING_WINDOW │ │ ──────────────────── │ │ Projections: │ │ ROW_NUMBER() OVER () │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ TOP_N │ │ ──────────────────── │ │ Top: 100 │ │ │ │ Order By: │ │ vastdb." │ │/docs_sql_fn_string_nextval│ │ ".orders.id ASC │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Generate keys once, when you write.** Store the `gen_random_uuid()` value, or a key your application assigns, in a column. A comparison of that column with a constant is pushed down to VAST DataBase; a key computed again at query time isn't. - **Number only the rows you need.** Filter and `LIMIT` in the subquery, so `row_number()` runs on fewer rows. ## Limitations and errors - **No sequences.** `CREATE SEQUENCE` fails with `The operator 'CREATE_SEQUENCE' type=52 is currently not supported.`, and `nextval` and `currval` fail with `Catalog Error: Sequence with name ... does not exist!`. Use `row_number()` or `gen_random_uuid()`, or assign IDs in your application. - **No auto-increment column.** A column default of `nextval(...)` fails in the same way. Write the key with each row instead. - **`row_number()` numbers a result, not a table.** The numbers start at 1 in each query; `OVER (ORDER BY ...)` and `PARTITION BY` fail with `The operator 'WINDOW' type=6 is currently not supported.` See [row_number](/develop/docs/dev-database-reference-sql-functions-aggregate-row-number). ``` run(f"SELECT id, row_number() OVER (ORDER BY id) FROM {TABLE}") ``` ``` InternalError: The operator 'WINDOW' type=6 is currently not supported. ``` ## Similar functions | Function | Returns | Available | | --- | --- | --- | | `nextval(name)` | the next value of a sequence | No sequence can be created: always an error, `NULL` for a `NULL` name | | `currval(name)` | the value `nextval` last returned | No sequence can be created: always an error | | [row_number](/develop/docs/dev-database-reference-sql-functions-aggregate-row-number) `OVER ()` | 1, 2, 3, ... for the rows of a result | Yes | | [gen_random_uuid](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-gen-random-uuid) | a new random `UUID` on each call | Yes | ``` run("SELECT nextval('docs_seq')") run("SELECT currval('docs_seq')") run(f"SELECT id, row_number() OVER () FROM (SELECT id FROM {TABLE} ORDER BY id LIMIT 100)") run("SELECT typeof(gen_random_uuid())") ``` ``` InternalError: Catalog Error: Sequence with name docs_seq does not exist! InternalError: Catalog Error: Sequence with name docs_seq does not exist! [(1, 1), (2, 2), (3, 3)] [('UUID',)] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TICKETS}") run(f"DROP TABLE IF EXISTS {SCHEMA}.seq_t") run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] [] [] ``` ## See also - [row_number](/develop/docs/dev-database-reference-sql-functions-aggregate-row-number) and [gen_random_uuid](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-gen-random-uuid). - [Functions](/develop/docs/dev-database-reference-sql-functions-index): all the function families. - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.