UUID

A Universally Unique Identifier (UUID) is a 16-byte value used to uniquely identify records. Tinybird supports the UUID data type, which is internally treated as a sequence of 16 random bytes. At the SQL level, UUIDs are represented in the standard 8-4-4-4-12 format.

Tinybird does not validate that inserted UUIDs conform to a particular variant, treating them as generic 16-byte identifiers.

Example UUID value:

61f0c404-5cb3-11e7-907b-a6006ad3dba0

The default value for a UUID is an all-zero UUID. This value appears if a record is ingested into a Data Source with a UUID column, but no specific value is provided for that column.

00000000-0000-0000-0000-000000000000

UUIDs are sorted by their second half, which can lead to non-intuitive sorting behavior. Therefore, it is generally not recommended to use UUID columns directly as primary keys, sorting keys, or partition keys in your Data Sources if a natural, sequential sort order is desired.

Example of non-intuitive sorting:

SELECT uuid_column FROM my_data_source ORDER BY uuid_column LIMIT 5

Result:

┌─uuid_column──────────────────────────┐
│ 36a0b67c-b74a-4640-803b-e44bb4547e3c │
│ 3a00aeb8-2605-4eec-8215-08c0ecb51112 │
│ 3fda7c49-282e-421a-85ab-c5684ef1d350 │
│ 16ab55a7-45f6-44a8-873c-7a0b44346b3e │
│ e3776711-6359-4f22-878d-bf290d052c85 │
└──────────────────────────────────────┘

As a workaround for sorting, you can convert the UUID to a type with a more intuitive sort order, such as UInt128.

Example using conversion to UInt128 for sorting:

SELECT uuid_column FROM my_data_source ORDER BY toUInt128(uuid_column) LIMIT 5

Result:

┌─uuid_column──────────────────────────┐
│ 018b81cd-aca1-4e9c-9e56-a84a074dc1a8 │
│ 02380033-c96a-438e-913f-a2c67e341def │
│ 057cf435-7044-456a-893b-9183a4475cea │
│ 0a3c1d4c-f57d-44cc-8567-60cb0c46f76e │
│ 0c15bf1c-8633-4414-a084-7017eead9e41 │
└──────────────────────────────────────┘

Generating UUIDs

You can generate random UUID version 4 values using the generateUUIDv4() function. For more details, see the UUID functions reference.

Usage examples

Example 1: Generating a UUID

This example demonstrates how to generate a new UUID within a SELECT query.

SELECT generateUUIDv4() AS new_uuid

Result:

┌─new_uuid─────────────────────────────┐
│ 417ddc5d-e556-4d27-95dd-a34d84e46a50 │
└──────────────────────────────────────┘

Example 2: Querying a default UUID

This example shows how a default (all-zero) UUID might appear in a Data Source if a record was ingested without a specified value for the UUID column.

SELECT uuid_column, some_string_column FROM my_data_source WHERE uuid_column = '00000000-0000-0000-0000-000000000000' LIMIT 2

Result:

┌─uuid_column──────────────────────────┬─some_string_column─┐
│ 417ddc5d-e556-4d27-95dd-a34d84e46a50 │ Example 1          │
│ 00000000-0000-0000-0000-000000000000 │ Example 2          │
└──────────────────────────────────────┴────────────────────┘

Restrictions

The UUID data type supports functions that are also applicable to the String data type, such as min, max, and count.

However, UUIDs do not support arithmetic operations (e.g., abs) or standard aggregate functions like sum and avg.

Updated