--- title: "UUID values and casts" slug: "dev-database-reference-sql-functions-binary-hash-uuid-uuid-values" updated: 2026-10-30T18:18:51Z published: 2026-10-30T18:18:51Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-binary-hash-uuid-uuid-values" --- > ## 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. # UUID values and casts *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.* A `UUID` value is a 128-bit identifier. You write one as text, cast to `UUID`: `'6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'::UUID`, `CAST(s AS UUID)` or `UUID '...'`. `CAST(u AS VARCHAR)` turns it back into the standard 36-character text. In a comparison with a `UUID` column, or an insert into one, text is converted for you. ## Syntax ``` 'text'::UUID CAST(text AS UUID) UUID 'text' CAST(u AS VARCHAR) ``` | Argument | Type | Description | | --- | --- | --- | | `text` | `VARCHAR` | 32 hexadecimal digits, in either case, with or without hyphens, optionally in braces. | | `u` | `UUID` | A `UUID` value or column. | **Returns:** `UUID`, which the client receives as a Python `uuid.UUID` object; or `VARCHAR`, 36 lowercase characters with hyphens. ## 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_uuid_values`. 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 six devices with a `UUID` column. The UUIDs are inserted as text in different forms: row 4 in uppercase, row 6 in braces without hyphens. Row 3 has no UUID: ``` 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_uuid_values"' 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}.devices" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, u UUID, name VARCHAR)") run(f"INSERT INTO {TABLE} VALUES " "(1, '6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f', 'alpha'), " "(2, '00000000-0000-0000-0000-000000000000', 'zero'), " "(3, NULL, 'unassigned'), " "(4, 'B3E1F0C2-7D4A-4E5B-8C6D-1F2E3A4B5C6D', 'bravo'), " "(5, 'ffffffff-ffff-ffff-ffff-ffffffffffff', 'max'), " "(6, '{a0eebc999c0b4ef8bb6d6bb9bd380a11}', 'charlie')") ``` ``` [] [] [(6,)] ``` ## What it does A cast to `UUID` parses text into a 128-bit value; a cast to `VARCHAR` writes it back in the standard form. - **Accepted text:** 32 hexadecimal digits in uppercase or lowercase, with the usual hyphens or without them, optionally in braces: `{6F1C2A4E8B3D4C1A9E2F0A1B2C3D4E5F}` is the same UUID as `6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f`. - **Not accepted:** a leading space, a `urn:uuid:` prefix, too few or too many digits, characters other than hex digits, and the empty string. Each fails with `Could not convert string '...' to INT128`. - **The text form is always lowercase with hyphens,** whatever form the value was written in. - **Text converts for you** where a `UUID` is expected: `u = 'B3E1F0C2-...'` compares as UUIDs, so case doesn't matter, and an `INSERT` of text into a `UUID` column parses it. - **UUIDs compare and sort as unsigned 128-bit numbers,** which is the same order as their lowercase text: `00000000-...` is the smallest, `ffffffff-...` the largest, and `7fffffff-...` is less than `80000000-...`. `min()` and `max()` work on a `UUID` column. ## NULLs and edge cases | Input | Result | | --- | --- | | `NULL::UUID`, `CAST(NULL::UUID AS VARCHAR)` | `NULL`, `NULL` | | `CAST('6F1C2A4E-8B3D-4C1A-9E2F-0A1B2C3D4E5F'::UUID AS VARCHAR)` | `'6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'` | | `CAST('6f1c2a4e8b3d4c1a9e2f0a1b2c3d4e5f'::UUID AS VARCHAR)`, the same in braces | `'6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'` | | `'6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'::UUID = '6F1C2A4E-8B3D-4C1A-9E2F-0A1B2C3D4E5F'` | `true` | | `'7fffffff-ffff-ffff-ffff-ffffffffffff'::UUID < '80000000-0000-0000-0000-000000000000'::UUID` | `true` | | `UUID '6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'` | the same as the `::UUID` cast | | `'6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'::UUID` `\|\|` `'x'` | `'6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5fx'`, a `VARCHAR` | | `TRY_CAST('not-a-uuid' AS UUID)` | `NULL` | | `' 6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'::UUID` (leading space), `''::UUID` | Error: `Could not convert string '...' to INT128` | 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-uuid-values#set-up-the-examples): ``` run("SELECT NULL::UUID, CAST(NULL::UUID AS VARCHAR), CAST('6F1C2A4E-8B3D-4C1A-9E2F-0A1B2C3D4E5F'::UUID AS VARCHAR), " "CAST('6f1c2a4e8b3d4c1a9e2f0a1b2c3d4e5f'::UUID AS VARCHAR), " "CAST('{6f1c2a4e8b3d4c1a9e2f0a1b2c3d4e5f}'::UUID AS VARCHAR)") run("SELECT '6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'::UUID = '6F1C2A4E-8B3D-4C1A-9E2F-0A1B2C3D4E5F', " "'7fffffff-ffff-ffff-ffff-ffffffffffff'::UUID < '80000000-0000-0000-0000-000000000000'::UUID, " "UUID '6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f' = '6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'::UUID") run("SELECT '6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'::UUID || 'x', TRY_CAST('not-a-uuid' AS UUID)") run("SELECT ' 6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'::UUID") run("SELECT ''::UUID") ``` ``` [(None, None, '6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f', '6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f', '6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f')] [(True, True, True)] [('6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5fx', None)] OSError: Could not convert string ' 6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f' to INT128 OSError: Could not convert string '' to INT128 ``` ## Examples Read the UUIDs as `UUID` objects and as text. Rows 4 and 6 were inserted in other forms and come back in the standard one: ``` run(f"SELECT id, u, CAST(u AS VARCHAR) FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, UUID('6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'), '6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'), (2, UUID('00000000-0000-0000-0000-000000000000'), '00000000-0000-0000-0000-000000000000'), (3, None, None), (4, UUID('b3e1f0c2-7d4a-4e5b-8c6d-1f2e3a4b5c6d'), 'b3e1f0c2-7d4a-4e5b-8c6d-1f2e3a4b5c6d'), (5, UUID('ffffffff-ffff-ffff-ffff-ffffffffffff'), 'ffffffff-ffff-ffff-ffff-ffffffffffff'), (6, UUID('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'), 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11')] ``` Look a device up by its UUID, written in any accepted form, and find the UUIDs in the upper half of the range: ``` run(f"SELECT id, name FROM {TABLE} WHERE u = 'b3e1f0c2-7d4a-4e5b-8c6d-1f2e3a4b5c6d'") run(f"SELECT id, name FROM {TABLE} WHERE u = '{{A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11}}'") run(f"SELECT id FROM {TABLE} WHERE u > '80000000-0000-0000-0000-000000000000' ORDER BY id LIMIT 10") ``` ``` [(4, 'bravo')] [(6, 'charlie')] [(4,), (5,), (6,)] ``` Sort by UUID, and get the smallest and largest: ``` run(f"SELECT id, u FROM {TABLE} WHERE u IS NOT NULL ORDER BY u LIMIT 10") run(f"SELECT min(u), max(u) FROM {TABLE}") ``` ``` [(2, UUID('00000000-0000-0000-0000-000000000000')), (1, UUID('6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f')), (6, UUID('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11')), (4, UUID('b3e1f0c2-7d4a-4e5b-8c6d-1f2e3a4b5c6d')), (5, UUID('ffffffff-ffff-ffff-ffff-ffffffffffff'))] [(UUID('00000000-0000-0000-0000-000000000000'), UUID('ffffffff-ffff-ffff-ffff-ffffffffffff'))] ``` Text that isn't a UUID stops the statement, in an `INSERT` as in a query. `TRY_CAST` gives NULL instead: ``` run(f"INSERT INTO {TABLE} (id, u) VALUES (98, 'not-a-uuid')") run(f"SELECT id FROM {TABLE} WHERE u = 'not-a-uuid'") run("SELECT TRY_CAST('not-a-uuid' AS UUID)") ``` ``` InternalError: Conversion Error: Could not convert string 'not-a-uuid' to INT128 OSError: Could not convert string 'not-a-uuid' to INT128 [(None,)] ``` ## How it runs in VAST The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (`VAST_SCAN`). A UUID written as text is converted to a `UUID` constant. What happens next depends on the condition: - **Pushed down to VAST DataBase:** a comparison of the `UUID` column with one constant, such as `u = '...'` or `u > '...'`, and `u IS NULL`. Rows that don't match never leave storage. - **In the Query Engine:** `u IN (...)` with several UUIDs, and any condition on `CAST(u AS VARCHAR)` or another function of the column. The scan sends every row, and a `FILTER` step tests each one. The plan of an equality with a text constant has no `FILTER` step; the plan of the same test on the column cast to text has one: ``` plan(f"SELECT id FROM {TABLE} WHERE u = '6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'") plan(f"SELECT id FROM {TABLE} WHERE CAST(u AS VARCHAR) = '6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'") ``` ``` ┌───────────────────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: id │ │ │ └───────────────────────────┘ ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (CAST(u AS VARCHAR) = │ │ '6f1c2a4e-8b3d-4c1a-9e2f │ │ -0a1b2c3d4e5f') │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ u │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Compare the `UUID` column with a constant, not its text.** `u = '6f1c...'` is pushed down; `CAST(u AS VARCHAR) = '6f1c...'` isn't. Since the constant is parsed as a UUID, you don't need to normalise its case or hyphens yourself. - **Store UUIDs in a `UUID` column.** Comparisons with a constant push down, any accepted text form matches, and the client gets `uuid.UUID` objects. - **Expect `IN` with several UUIDs to run in the Query Engine.** It returns the right rows, but each row of the scan is tested in a `FILTER` step: ``` plan(f"SELECT id FROM {TABLE} WHERE u IN ('6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f', " "'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11')") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ ((u = '6f1c2a4e-8b3d-4c1a │ │ -9e2f-0a1b2c3d4e5f'::UUID)│ │ OR (u = 'a0eebc99-9c0b │ │ -4ef8-bb6d-6bb9bd380a11': │ │ :UUID)) │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ u │ │ id │ │ │ └───────────────────────────┘ ``` ## Limitations and errors - **Text and `UUID` convert to each other only.** A cast between `UUID` and a number (including `HUGEINT`), `BLOB`, `BOOLEAN`, a date or time type fails with `Unimplemented type for cast`, followed by the two types. Go through `VARCHAR`. - **Text functions need a cast.** `length(u)` and other string functions fail with `No function matches the given name and argument types`; use `CAST(u AS VARCHAR)`. The `||` operator converts the UUID to text for you. - **Invalid text stops the statement.** In a cast, a comparison or an `INSERT`, text that isn't a UUID fails with `Could not convert string '...' to INT128` (in an `INSERT`, prefixed with `Conversion Error:`). Trim spaces and remove prefixes such as `urn:uuid:` first, or use [TRY_CAST](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-try-cast) to get NULL. ``` run("SELECT CAST('6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'::UUID AS BLOB)") run("SELECT length('6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'::UUID)") run("SELECT 'urn:uuid:6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'::UUID") ``` ``` OSError: Unimplemented type for cast (UUID -> BLOB) InternalError: Binder Error: No function matches the given name and argument types 'length(UUID)'. You might need to add explicit type casts. OSError: Could not convert string 'urn:uuid:6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f' to INT128 ``` ## Similar functions | Expression | Gives | | --- | --- | | `s::UUID`, `CAST(s AS UUID)` | the UUID in `s`; an error if `s` isn't one | | [TRY_CAST](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-try-cast)`(s AS UUID)` | the UUID in `s`; NULL if `s` isn't one | | `CAST(u AS VARCHAR)` | the UUID as 36 lowercase characters | | `upper(CAST(u AS VARCHAR))` | the UUID as 36 uppercase characters | | [gen_random_uuid](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-gen-random-uuid)`()` | a new random UUID | ``` run("SELECT CAST('6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f' AS UUID), TRY_CAST('6f1c' AS UUID), " "upper(CAST('6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'::UUID AS VARCHAR)), typeof(gen_random_uuid())") ``` ``` [(UUID('6f1c2a4e-8b3d-4c1a-9e2f-0a1b2c3d4e5f'), None, '6F1C2A4E-8B3D-4C1A-9E2F-0A1B2C3D4E5F', 'UUID')] ``` 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. - [CAST and ::](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-cast): conversions between other types. - [Data types in SQL](/develop/docs/dev-database-reference-sql-data-types): `UUID` columns. - [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns). - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.