All Products
Search

This Product

    AnalyticDB for PostgreSQL:Use the uuid-ossp extension

    Document Center

    AnalyticDB for PostgreSQL:Use the uuid-ossp extension

    Last Updated:Apr 12, 2024

    This topic describes how to use the uuid-ossp extension in AnalyticDB for PostgreSQL.

    Important

    To install or upgrade extensions on AnalyticDB for PostgreSQL instances of V6.3.8.9 or later, contact technical support.

    For information about how to view the minor version of an AnalyticDB for PostgreSQL instance, see View the minor engine version.

    Introduction

    The UUID data type is used to store universally unique identifiers (UUIDs). UUIDs are more unique than sequences in distributed systems.

    A UUID consists of 32 hexadecimal digits. The standard format is a group of eight characters followed by three groups of four digits and then a group of 12 digits. The groups are separated by hyphens (-). Example:

    a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

    You can use uppercase letters or braces ({}) to enclose a UUID in standard format, omit some or all hyphens (-), or add a hyphen after any group of four digits. Examples:

    A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11
{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}
a0eebc999c0b4ef8bb6d6bb9bd380a11
a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11
{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}
    Note

    The available versions of AnalyticDB for PostgreSQL do not allow you to use a field of the UUID data type as the distribution key.

    For information about how to build uuid-ossp, see Building uuid-ossp.

    Installation

    Execute the following statement to install uuid-ossp:

    CREATE EXTENSION "uuid-ossp";
    Note

    The account that is used to install uuid-ossp must be granted the rds_superuser permission.

    Functions

    • Functions for UUID generation

      Function

      Description

      uuid_generate_v1()

      This function generates a version 1 UUID. This involves the MAC address of a computer and a timestamp.

      Note

      This type of UUID displays the identity of the computer that created the identifier and the time when the identifier was created. This type of UUID is not suitable for security-sensitive applications.

      uuid_generate_v1mc()

      This function generates a version 1 UUID. This function differs from the uuid_generate_v1() function in that the uuid_generate_v1mc() function uses a random multicast MAC address to generate a UUID, whereas the uuid_generate_v1() function uses the real MAC address of a computer to generate a UUID.

      uuid_generate_v3(namespace uuid, name text)

      This function generates a version 3 UUID in the specified namespace by using the specified name.

      • The namespace is the constant that is returned by one of the uuid_ns_*() functions. The following table describes the functions.

      • The name is an identifier in the specified namespace.

      Example: 

      SELECT uuid_generate_v3(uuid_ns_url(), 'example.com');

      The name parameter is hashed by using the MD5 hashing algorithm. No plaintext can be derived from the generated UUID. A UUID that is generated by using this function does not require a random algorithm and is independent of the environment variables that are required to run the system. This allows you to reproduce the UUID.

      uuid_generate_v4()

      This function generates a version 4 UUID based on random numbers.

      uuid_generate_v5(namespace uuid, name text)

      This function generates a version 5 UUID in a similar manner that a version 3 UUID is generated. The SHA-1 hashing algorithm that is used to generate version 5 UUIDs is more secure than the MD5 hashing algorithm. Therefore, version 5 UUIDs are recommended.

    • Functions that return UUID constants

      Function

      Description

      uuid_nil()

      This function returns a nil UUID constant, which is not considered as a real UUID.

      uuid_ns_dns()

      This function returns a constant that designates the Domain Name System (DNS) namespace for UUIDs.

      uuid_ns_url()

      This function returns a constant that designates the URL namespace for UUIDs.

      uuid_ns_oid()

      This function returns a constant that designates the ISO object identifier (OID) namespace for UUIDs.

      Note

      The ISO OID is defined by using the Abstract Syntax Notation One (ASN.1) standard and is different from the OIDs used in PostgreSQL.

      uuid_ns_x500()

      This function returns a constant that designates the X.500 distinguished name (DN) namespace for UUIDs.

    Examples

    • Execute the following statement to generate a version 1 UUID: 

      SELECT uuid_generate_v1();

      Sample result:

                 uuid_generate_v1
--------------------------------------
 c7f83ba4-bd93-11e9-8674-40a8f01ec4e8
(1 row)

    • Execute the following statement to generate a version 3 UUID: 

      SELECT uuid_generate_v3(uuid_ns_url(), 'example.com');

      Sample result:

                 uuid_generate_v3
--------------------------------------
 a0473a67-27a1-3c05-a2d1-5c134639347f
(1 row)

    • Execute the following statement to generate a version 4 UUID: 

      SELECT uuid_generate_v4();

      Sample result:

                 uuid_generate_v4
--------------------------------------
 d7a8d47e-58e3-4bd9-9340-8553ac03d144
(1 row)

    • Execute the following statement to generate a version 5 UUID:

      SELECT uuid_generate_v5(uuid_ns_url(), 'example.com');

      Sample result:

                 uuid_generate_v5
--------------------------------------
 a5cf6e8e-4cfa-5f31-a804-6de6d1245e26
(1 row)

    References