Functions for Working with UUID

    Generates the UUID of .

    Returned value

    The UUID type value.

    Usage example

    This example demonstrates creating a table with the UUID type column and inserting a value into the table.

    3. INSERT INTO t_uuid SELECT generateUUIDv4()
    5. SELECT * FROM t_uuid
    1. ┌────────────────────────────────────x─┐
    2. │ f4bf890f-f9dc-4332-ad5c-0c18e73f28e9 │
    3. └──────────────────────────────────────┘

    empty

    Checks whether the input UUID is empty.

    Syntax

    1. empty(UUID)

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

    The function also works for or strings.

    Arguments

    Returned value

    • Returns 1 for an empty UUID or 0 for a non-empty UUID.

    Type: .

    Example

    To generate the UUID value, ClickHouse provides the generateUUIDv4 function.

    Query:

    1. SELECT empty(generateUUIDv4());

    Result:

    1. ┌─empty(generateUUIDv4())─┐
    2. │                       0 │
    3. └─────────────────────────┘

    notEmpty

    Syntax

    1. notEmpty(UUID)

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

    The function also works for arrays or .

    Arguments

    • — Input UUID. UUID.

    Returned value

    • Returns 1 for a non-empty UUID or 0 for an empty UUID.

    Type: .

    Example

    To generate the UUID value, ClickHouse provides the generateUUIDv4 function.

    Query:

    1. SELECT notEmpty(generateUUIDv4());

    Result:

    Converts String type value to UUID type.

    1. toUUID(String)

    Returned value

    The UUID type value.

    Usage example

    1. SELECT toUUID('61f0c404-5cb3-11e7-907b-a6006ad3dba0') AS uuid
    1. ┌─────────────────────────────────uuid─┐
    2. │ 61f0c404-5cb3-11e7-907b-a6006ad3dba0 │
    3. └──────────────────────────────────────┘

    toUUIDOrNull (x)

    It takes an argument of type String and tries to parse it into UUID. If failed, returns NULL.

    1. toUUIDOrNull(String)

    Returned value

    Usage example

    1. SELECT toUUIDOrNull('61f0c404-5cb3-11e7-907b-a6006ad3dba0T') AS uuid
    1. ┌─uuid─┐
    2. │ ᴺᵁᴸᴸ │
    3. └──────┘

    toUUIDOrZero (x)

    It takes an argument of type String and tries to parse it into UUID. If failed, returns zero UUID.

      Returned value

      The UUID type value.

      Usage example

      1. ┌─────────────────────────────────uuid─┐
      2. │ 00000000-0000-0000-0000-000000000000 │

      Accepts a string containing 36 characters in the format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, and returns it as a set of bytes in a .

      1. UUIDStringToNum(String)

      Returned value

      FixedString(16)

      Usage examples

      1. SELECT
      2.     '612f3c40-5d3b-217e-707b-6a546a3d7b29' AS uuid,
      3.     UUIDStringToNum(uuid) AS bytes
      1. ┌─uuid─────────────────────────────────┬─bytes────────────┐
      2. │ 612f3c40-5d3b-217e-707b-6a546a3d7b29 │ a/<@];!~p{jTj={) │
      3. └──────────────────────────────────────┴──────────────────┘

      UUIDNumToString

      Accepts a value, and returns a string containing 36 characters in text format.

      1. UUIDNumToString(FixedString(16))

      Returned value

      String.

      Usage example

      1. SELECT
      2.     'a/<@];!~p{jTj={)' AS bytes,
      3.     UUIDNumToString(toFixedString(bytes, 16)) AS uuid
      1. ┌─bytes────────────┬─uuid─────────────────────────────────┐
      2. │ a/<@];!~p{jTj={) │ 612f3c40-5d3b-217e-707b-6a546a3d7b29 │
      3. └──────────────────┴──────────────────────────────────────┘

      serverUUID()

      Returns the random and unique UUID, which is generated when the server is first started and stored forever. The result writes to the file uuid created in the ClickHouse server directory .

      Syntax

      Returned value

      • The UUID of the server.