UUID (Universally Unique Identifier) functions allow you to generate, manipulate, and work with UUIDs. These functions are useful for creating unique identifiers for records, tracking distributed transactions, or generating time-ordered unique IDs.

This page covers various UUID-related functions, including:

  • Generating UUIDs (versions 4 and 7)
  • Converting between UUID formats (string, binary, and native UUID type)
  • Extracting information from UUIDs (such as timestamps from version 7)

​
ClickHouse function reference

​
generateUUIDv4

Generates a version 4 UUID (Universally Unique Identifier).

Syntax:

generateUUIDv4([expr])

Arguments:

  • expr (any type, optional): An optional expression used to bypass common subexpression elimination if the function is called multiple times in a query. The value of the expression does not affect the returned UUID.

Returns:

A value of type UUID.

Example:

SELECT
	generateUUIDv4() AS random_uuid,
	generateUUIDv4('taco') AS taco_uuid;

Result:

| random_uuid                           | taco_uuid                             |
|---------------------------------------|---------------------------------------|
| f4bf890f-f9dc-4332-ad5c-0c18e73f28e9  | 2d49dc6e-ddce-4cd0-afb8-790956df54c1  |

In this example, we generate two UUIDs: one without an argument and one with a ‘taco’ argument. The ‘taco’ argument doesn’t affect the UUID generation but ensures a unique UUID is created each time the function is called.

UUIDs generated by this function are random and are not guaranteed to be unique across multiple function calls or across distributed systems. For applications requiring guaranteed uniqueness, consider using a centralized UUID generation service or a different UUID version.

​
generateUUIDv7

Generates a version 7 UUID (Universally Unique Identifier).

Syntax:

generateUUIDv7([expr])

Arguments:

  • expr (any type, optional): An arbitrary expression used to bypass common subexpression elimination if the function is called multiple times in a query. The value of the expression does not affect the returned UUID.

Returns:

A value of type UUIDv7.

Description:

The generated UUIDv7 contains the following components:

  • Current Unix timestamp in milliseconds (48 bits)
  • Version “7” (4 bits)
  • Counter (42 bits, including a 2-bit variant field “2”) to distinguish UUIDs within a millisecond
  • Random field (32 bits)

The function guarantees that the counter field within a timestamp increments monotonically across all function invocations in concurrently running threads and queries.

As of April 2024, version 7 UUIDs are in draft status and their layout may change in the future.

Example:

SELECT generateUUIDv7() AS taco_order_id;

Result:

| taco_order_id                         |
|---------------------------------------|
| 018f05af-f4a8-778f-beee-1bedbc95c93b  |

In this example, we generate a UUIDv7 as a unique identifier for a taco order.

Example with multiple UUIDs:

SELECT
	generateUUIDv7(1) AS first_taco_uuid,
	generateUUIDv7(2) AS second_taco_uuid;

Result:

| first_taco_uuid                       | second_taco_uuid                      |
|---------------------------------------|---------------------------------------|
| 018f05c9-4ab8-7b86-b64e-c9f03fbd45d1  | 018f05c9-4ab8-7b86-b64e-c9f12efb7e16  |

This example demonstrates generating multiple UUIDs in a single query, which could be used for creating unique identifiers for a batch of taco orders.

​
empty

Checks whether the input UUID is empty.

Syntax:

empty(UUID)

Arguments:

  • UUID (UUID): A UUID value.

Returns:

  • 1 for an empty UUID (all zeros), 0 for a non-empty UUID. [UInt8]

Example:

SELECT empty(generateUUIDv4()) AS is_empty;

Result:

| is_empty |
|----------|
| 0        |

This example generates a new UUIDv4 and checks if it’s empty. The result is 0 because a newly generated UUID is not empty.

The function also works for Arrays and Strings.

​
notEmpty

Checks whether the input UUID is non-empty.

Syntax:

notEmpty(UUID)

Arguments:

  • UUID (UUID): A UUID value.

Returns:

  • 1 for a non-empty UUID or 0 for an empty UUID. [UInt8]

A UUID is considered empty if it contains all zeros (zero UUID).

Example:

SELECT notEmpty(generateUUIDv4()) AS is_not_empty;

Result:

| is_not_empty |
|--------------|
| 1            |

In this example, we generate a new UUIDv4 and check if it’s not empty. The result is 1, indicating that the generated UUID is not empty.

This function also works for Arrays and Strings.

​
toUUID

Converts a string value to a UUID.

Syntax:

toUUID(string)

Arguments:

  • string (String): A string containing a UUID in the format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx.

Returns:

  • The UUID type value.

Example:

SELECT toUUID('61f0c404-5cb3-11e7-907b-a6006ad3dba0') AS taco_uuid

Result:

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

In this example, we convert a string representation of a UUID (perhaps representing a unique taco order ID) to a UUID type.

If the input string is not a valid UUID format, the function will throw an exception.

​
toUUIDOrDefault

Converts a string to a UUID, returning a default value if the conversion fails.

Syntax:

toUUIDOrDefault(string, default)

Arguments:

  • string (String) — A string containing a UUID.
  • default (UUID) — The UUID to return if the conversion fails.

Returns:

The UUID value converted from the string, or the default value if conversion fails.

Example:

SELECT
	toUUIDOrDefault('61f0c404-5cb3-11e7-907b-a6006ad3dba0', toUUID('00000000-0000-0000-0000-000000000000')) AS uuid,
	toUUIDOrDefault('not-a-uuid', toUUID('00000000-0000-0000-0000-000000000000')) AS default_uuid;

Result:

| uuid                                   | default_uuid                            |
|----------------------------------------|-----------------------------------------|
| 61f0c404-5cb3-11e7-907b-a6006ad3dba0   | 00000000-0000-0000-0000-000000000000    |

In this example:

  • The first toUUIDOrDefault call successfully converts a valid UUID string.
  • The second call returns the default UUID because ‘not-a-uuid’ is not a valid UUID string.

This function is useful when you need to handle potentially invalid UUID strings in your data, providing a fallback value to ensure query continuity.

​
toUUIDOrNull

Attempts to convert a string to a UUID. If the conversion fails, it returns NULL.

Syntax:

toUUIDOrNull(string)

Arguments:

  • string (String): A string containing a UUID representation.

Returns:

The UUID value if the conversion is successful, or NULL if it fails. [Nullable(UUID)]

Example:

SELECT
	toUUIDOrNull('61f0c404-5cb3-11e7-907b-a6006ad3dba0') AS valid_uuid,
	toUUIDOrNull('not-a-uuid') AS invalid_uuid,
	toUUIDOrNull('61f0c404-5cb3-11e7-907b-a6006ad3dba0T') AS malformed_uuid
FROM
	taco_orders;

Result:

| valid_uuid                             | invalid_uuid | malformed_uuid |
|----------------------------------------|--------------|----------------|
| 61f0c404-5cb3-11e7-907b-a6006ad3dba0   | NULL         | NULL           |

In this example:

  • valid_uuid successfully converts a valid UUID string.
  • invalid_uuid returns NULL for a completely invalid input.
  • malformed_uuid returns NULL for a UUID-like string with an extra character.

This function is useful when you need to handle potentially invalid UUID strings without causing errors in your query.

​
toUUIDOrZero

Converts a string to a UUID. If the conversion fails, it returns a zero UUID.

Syntax:

toUUIDOrZero(string)

Arguments:

  • string (String) — A string containing a UUID.

Returns:

  • The UUID value if the conversion is successful, or a zero UUID (00000000-0000-0000-0000-000000000000) if the conversion fails. [UUID]

Example:

SELECT
	toUUIDOrZero('61f0c404-5cb3-11e7-907b-a6006ad3dba0') AS valid_uuid,
	toUUIDOrZero('not-a-uuid') AS invalid_uuid,
	toUUIDOrZero('spicy-carnitas-taco') AS taco_uuid;

Result:

| valid_uuid                             | invalid_uuid                           | taco_uuid                            |
|----------------------------------------|----------------------------------------|--------------------------------------|
| 61f0c404-5cb3-11e7-907b-a6006ad3dba0   | 00000000-0000-0000-0000-000000000000   | 00000000-0000-0000-0000-000000000000 |

In this example:

  • valid_uuid successfully converts a valid UUID string.
  • invalid_uuid returns a zero UUID for an invalid input.
  • taco_uuid also returns a zero UUID, as “spicy-carnitas-taco” is not a valid UUID format.

This function is useful when you need to ensure that invalid UUID strings are converted to a consistent default value (zero UUID) rather than causing an error.

​
UUIDStringToNum

Converts a UUID string to its binary representation.

Syntax:

UUIDStringToNum(uuid[, variant = 1])

Arguments:

  • uuid (String): A string containing a UUID in the format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx.
  • variant (UInt8, optional): Integer specifying the byte order variant.
    • 1 = Big-endian (default)
    • 2 = Microsoft

Returns:

The binary representation of the UUID. (FixedString(16))

Example:

SELECT
	'61f0c404-5cb3-11e7-907b-a6006ad3dba0' AS uuid,
	UUIDStringToNum(uuid) AS binary_uuid

Result:

| uuid                                 | binary_uuid   |
|--------------------------------------|---------------|
| 61f0c404-5cb3-11e7-907b-a6006ad3dba0 | a�Ĵ\�ᮗ��m�    |

This example converts a UUID string representing a taco order ID to its binary representation. The binary output is displayed as a hexadecimal string for readability.

Example with Microsoft variant:

SELECT
	'61f0c404-5cb3-11e7-907b-a6006ad3dba0' AS uuid,
	UUIDStringToNum(uuid, 2) AS binary_uuid_ms

Result:

| uuid                                 | binary_uuid_ms |
|--------------------------------------|----------------|
| 61f0c404-5cb3-11e7-907b-a6006ad3dba0 | �Ĵa�\�ᮗ��m�    |

This example uses the Microsoft variant, which changes the byte order of the first three components of the UUID.

The binary representation is typically used for efficient storage and indexing of UUIDs in databases.

​
UUIDNumToString

Converts a binary representation of a UUID to its string format.

Syntax:

UUIDNumToString(binary[, variant = 1])

Arguments:

  • binary (FixedString(16)): A binary representation of a UUID.
  • variant (UInt8, optional): Integer specifying the UUID format variant.
    • 1 = Big-endian (default)
    • 2 = Microsoft

Returns:

A string containing 36 characters representing the UUID in text format. (String)

Example:

SELECT
	'a/<@];!~p{jTj={)' AS bytes,
	UUIDNumToString(toFixedString(bytes, 16)) AS uuid

SELECT
	'@</a;]~!p{jTj={)' AS bytes,
	UUIDNumToString(toFixedString(bytes, 16), 2) AS uuid

Result:

| bytes            | uuid                                 |
|------------------|--------------------------------------|
| a/<@];!~p{jTj={) | 612f3c40-5d3b-217e-707b-6a546a3d7b29 |

| bytes            | uuid                                 |
|------------------|--------------------------------------|
| @</a;]~!p{jTj={) | 612f3c40-5d3b-217e-707b-6a546a3d7b29 |

In these examples, UUIDNumToString converts the binary representation of a UUID to its string format. The second example demonstrates using the Microsoft variant.

This function is particularly useful when you need to convert UUID values stored in binary format back to their human-readable string representation, such as when working with UUIDs in taco order systems or customer databases.

​
UUIDToNum

Converts a UUID to its binary representation as a FixedString(16).

Syntax

UUIDToNum(uuid[, variant = 1])

Arguments

  • uuid (UUID): Value of type UUID.
  • variant (UInt8, optional): Integer representing the byte order variant.
    • 1 = Big-endian (default)
    • 2 = Microsoft (little-endian)

Returns

  • A FixedString(16) containing the binary representation of the UUID.

Example

SELECT
	toUUID('612f3c40-5d3b-217e-707b-6a546a3d7b29') AS uuid,
	UUIDToNum(uuid) AS bytes;

Result:

| uuid                                 | bytes            |
|--------------------------------------|------------------|
| 612f3c40-5d3b-217e-707b-6a546a3d7b29 | a/<@];!~p{jTj={) |

Using the Microsoft variant:

SELECT
	toUUID('612f3c40-5d3b-217e-707b-6a546a3d7b29') AS uuid,
	UUIDToNum(uuid, 2) AS bytes;

Result:

| uuid                                 | bytes            |
|--------------------------------------|------------------|
| 612f3c40-5d3b-217e-707b-6a546a3d7b29 | @</a;]~!p{jTj={) |

This function is useful when you need to work with the binary representation of UUIDs, such as for storage optimization or when interfacing with systems that expect UUIDs in binary format.

​
UUIDv7ToDateTime

Returns the timestamp component of a UUID version 7.

Syntax:

UUIDv7ToDateTime(uuid[, timezone])

Arguments:

  • uuid (UUID): UUID of version 7.
  • timezone (String, optional): Timezone name for the returned value.

Returns:

  • Timestamp with milliseconds precision. If the UUID is not a valid version 7 UUID, it returns 1970-01-01 00:00:00.000. [DateTime64(3)]

Example:

SELECT
	UUIDv7ToDateTime(toUUID('018f05c9-4ab8-7b86-b64e-c9f03fbd45d1')) AS taco_order_time;

Result:

| taco_order_time         |
|-------------------------|
| 2024-04-22 15:30:29.048 |

This example extracts the timestamp from a UUIDv7, which could represent the time a taco order was placed.

SELECT
	UUIDv7ToDateTime(toUUID('018f05c9-4ab8-7b86-b64e-c9f03fbd45d1'), 'America/Los_Angeles') AS taco_order_time_la;

Result:

| taco_order_time_la      |
|-------------------------|
| 2024-04-22 08:30:29.048 |

This example shows the same taco order time converted to the Los Angeles timezone.

UUIDv7 contains a timestamp with millisecond precision, making it useful for generating time-based unique identifiers for events like taco orders.

Was this page helpful?

URLsOverview