UUID values and casts

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › 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)

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), 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), 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, "<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

The same cases, as the cluster returns them. They use the helpers from 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 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

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: all the UUID functions.
• CAST and ::: conversions between other types.
• Data types in SQL: UUID columns.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.