--- title: "gen_random_uuid" slug: "dev-database-reference-sql-functions-binary-hash-uuid-gen-random-uuid" updated: 2026-10-30T18:18:51Z published: 2026-10-30T18:18:51Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-binary-hash-uuid-gen-random-uuid" --- > ## 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. # gen_random_uuid *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Binary, hash and UUID functions](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid) › UUIDs.* Returns a new random version 4 UUID each time it's called, and a different one for each row. `uuid` is the same function. Use it to give rows an identifier when you write them. ## Syntax ``` gen_random_uuid() uuid() ``` The functions take no arguments. **Returns:** `UUID`. The client receives it as a Python `uuid.UUID` object. **Aliases:** `uuid`. ## 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_binary_hash_uuid_gen_random_uuid`. 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 items. Rows 1 and 2 get a UUID from `gen_random_uuid()` and `uuid()` as they are written; row 3 has none: ``` 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_binary_hash_uuid_gen_random_uuid"' 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 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}.items" V4 = "'^[0-9a-f]{8}-[0-9a-f]{4}-4[0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$'" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, u UUID, label VARCHAR)") run(f"INSERT INTO {TABLE} VALUES (1, gen_random_uuid(), 'first'), (2, uuid(), 'second'), (3, NULL, 'third')") ``` ``` [] [] [(3,)] ``` `V4` is the pattern of a version 4 UUID in text form: the third group starts with `4`, and the fourth with `8`, `9`, `a` or `b`. The examples test values against it instead of printing them, because every run gives new values. ## What it does `gen_random_uuid()` returns a random UUID of version 4, the version made of random bits. `uuid()` does the same. - **A new value for each call and each row.** Two calls in the same expression give different values, and a query over a table gives each row its own value. - **The result is a `UUID`,** not text. Store it in a `UUID` column, or cast it to `VARCHAR` for the 36-character form with hyphens, in lowercase. - **It's computed when the query runs.** A value you `SELECT` isn't kept: write it to a table to use it again. ## NULLs and edge cases | Input | Result | | --- | --- | | `gen_random_uuid() IS NULL` | `false`: it never returns NULL | | `gen_random_uuid() = gen_random_uuid()` | `false` | | `typeof(gen_random_uuid())`, `typeof(uuid())` | `'UUID'`, `'UUID'` | | `length(CAST(gen_random_uuid() AS VARCHAR))` | `36` | | `substring(CAST(uuid() AS VARCHAR), 15, 1)` | `'4'`, the version digit | | `regexp_matches(CAST(gen_random_uuid() AS VARCHAR), V4)` | `true` | | `gen_random_uuid(1)`, `uuid('x')` | 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-binary-hash-uuid-gen-random-uuid#set-up-the-examples): ``` run("SELECT gen_random_uuid() IS NULL, gen_random_uuid() = gen_random_uuid(), " "typeof(gen_random_uuid()), typeof(uuid())") run(f"SELECT length(CAST(gen_random_uuid() AS VARCHAR)), substring(CAST(uuid() AS VARCHAR), 15, 1), " f"regexp_matches(CAST(gen_random_uuid() AS VARCHAR), {V4})") run("SELECT gen_random_uuid(1)") run("SELECT uuid('x')") ``` ``` [(False, False, 'UUID', 'UUID')] [(36, '4', True)] InternalError: Binder Error: No function matches the given name and argument types 'gen_random_uuid(INTEGER_LITERAL)'. You might need to add explicit type casts. InternalError: Binder Error: No function matches the given name and argument types 'uuid(STRING_LITERAL)'. You might need to add explicit type casts. ``` ## Examples Check the UUIDs written by the setup block. Rows 1 and 2 hold version 4 UUIDs; row 3 has none: ``` run(f"SELECT id, u IS NOT NULL, regexp_matches(CAST(u AS VARCHAR), {V4}) FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, True, True), (2, True, True), (3, False, None)] ``` Write 1,000 rows with a UUID each in one statement, using the [range](/develop/docs/dev-database-reference-sql-functions-table-functions-range) table function, and check them. Every row gets a value of the version 4 form: ``` run(f"INSERT INTO {TABLE} SELECT 100 + x, gen_random_uuid(), 'bulk' FROM range(1000) t(x)") run(f"SELECT count(*), count(u), sum(CASE WHEN regexp_matches(CAST(u AS VARCHAR), {V4}) THEN 1 ELSE 0 END) " f"FROM {TABLE} WHERE id >= 100") ``` ``` [(1000,)] [(1000, 1000, Decimal('1000'))] ``` Give the row that has no UUID one, with `UPDATE`: ``` run(f"UPDATE {TABLE} SET u = gen_random_uuid() WHERE id = 3") run(f"SELECT id, u IS NOT NULL FROM {TABLE} WHERE id = 3") ``` ``` [(1,)] [(3, True)] ``` Two calls in one row give different values. Out of 100 rows with two UUIDs each, none has a matching pair: ``` run("SELECT count(*) FROM (SELECT gen_random_uuid() AS a, gen_random_uuid() AS b FROM range(100)) WHERE a = b") ``` ``` [(0,)] ``` ## How it runs in VAST The Query Engine runs on the cluster's CNodes. `gen_random_uuid()` reads no data: the Query Engine computes a new value for every row it produces. - **In the `SELECT` list or in `INSERT ... SELECT`,** it's computed for each row the query returns or writes. - **In `WHERE`,** a condition that uses it, such as `u = gen_random_uuid()`, is a `FILTER` step in the Query Engine. Unlike a constant, it isn't computed once before the scan, so the comparison isn't pushed down to VAST DataBase. The plan of such a condition shows the `FILTER` step above the scan: ``` plan(f"SELECT id FROM {TABLE} WHERE u = gen_random_uuid()") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (u = gen_random_uuid()) │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ u │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Generate the UUID when you write the row,** with `INSERT` or `UPDATE` as in the examples, and keep it in a `UUID` column. Reading it back costs nothing extra, and a comparison of the column with a UUID constant is pushed down to VAST DataBase ([UUID values and casts](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-uuid-values)). - **Write many rows in one statement.** `INSERT ... SELECT ..., gen_random_uuid() FROM range(n)` creates all the rows and their UUIDs in one query. ## Limitations and errors - **Version 4 only.** `uuidv4`, `uuidv7`, `uuid_generate_v4`, `uuid_extract_version` and `uuid_extract_timestamp` fail with `Scalar Function with name ... does not exist!`. Use `gen_random_uuid()` for version 4 UUIDs, and generate other versions in your application. - **No arguments.** `gen_random_uuid(1)` and `uuid('x')` fail with `No function matches the given name and argument types`. - **No duplicate check with `GROUP BY`.** `GROUP BY` isn't available in the VAST Query Engine (`The operator 'HASH_GROUP_BY' type=9 is currently not supported.`), so you can't count distinct UUIDs in SQL. If your application needs a uniqueness guarantee, check it when you load the data. ``` run("SELECT uuidv7()") run(f"SELECT u, count(*) FROM {TABLE} GROUP BY u ORDER BY u LIMIT 10") ``` ``` InternalError: Catalog Error: Scalar Function with name uuidv7 does not exist! InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported. ``` ## Similar functions | Expression | Gives | Type | | --- | --- | --- | | `gen_random_uuid()` | a new random UUID | `UUID` | | `uuid()` | the same as `gen_random_uuid()` | `UUID` | | `'6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'::UUID` | a fixed UUID from text ([UUID values and casts](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-uuid-values)) | `UUID` | | `CAST(gen_random_uuid() AS VARCHAR)` | a new random UUID as 36 characters of text | `VARCHAR` | ``` run("SELECT typeof(gen_random_uuid()), typeof(uuid()), typeof('6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'::UUID), " "typeof(CAST(gen_random_uuid() AS VARCHAR))") ``` ``` [('UUID', 'UUID', 'UUID', 'VARCHAR')] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [Binary, hash and UUID functions](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid): all the UUID functions. - [UUID values and casts](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-uuid-values): UUID constants, text forms and comparisons. - [Data types in SQL](/develop/docs/dev-database-reference-sql-data-types): `UUID` columns. - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.