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

Important To install or upgrade extensions on an instance that runs V6.3.8.9 or later, Submit a ticket.

For more information about how to view the minor version of an 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 followed by a group of 12 digits. The groups are separated by hyphens (-). Example:

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

You can also 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. Example:

A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11
{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}
a0eebc999c0b4ef8bb6d6bb9bd380a11
a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11
{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}
Note None of the available versions of AnalyticDB for PostgreSQL allow you to use a field of the UUID data type as the distribution key.

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
    FunctionDescription
    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 reveals the identity of the computer that created the identifier and the time at which the identifier was created. Therefore, this type of UUID is unsuitable 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 one of the constants that are returned by the uuid_ns_*() functions described in the Functions returning UUID constants table.
    • The name is an identifier in the specified namespace.
    Example:
    SELECT uuid_generate_v3(uuid_ns_url(), 'example.com');

    The name parameter is hashed based on the MD5 hashing algorithm. Therefore, no plaintext can be derived from the generated UUID. A UUID that is generated by using this function does not require a random algorithm. The UUID is not generated based on the environment variables that are required to run the system. As a result, the UUID can be reproduced.

    uuid_generate_v4()This function generates a version 4 UUID, which is generated based on random numbers.
    uuid_generate_v5(namespace uuid, name text)This function generates a version 5 UUID, which is similar to a version 3 UUID except that the SHA-1 hashing algorithm is used. Version 5 is preferred over version 3 because SHA-1 is considered to be more secure than MD5.
  • Functions returning UUID constants
    FunctionDescription
    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 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 This pertains to ASN.1 OIDs, which are unrelated to 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();

    The following result is returned:

               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');

    The following result is returned:

               uuid_generate_v3
    --------------------------------------
     a0473a67-27a1-3c05-a2d1-5c134639347f
    (1 row)
  • Execute the following statement to generate a version 4 UUID:
    SELECT uuid_generate_v4();

    The following result is returned:

               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');

    The following result is returned:

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

References