This topic describes how to use the UUID-OSSP extension to generate universally unique identifiers (UUID).

Description of the UUID-OSSP extension

The UUID-OSSP extension supports several standard algorithms that are used to generate UUIDs. The UUID-OSSP extension also provides functions to generate some special UUID constants.

A standard UUID has 32 hexadecimal digits plus 4 hyphens (-) for a total of 36 characters, which are written in five groups separated by hyphens (-). A UUID is in the following format: Group of 8 characters-Group of 4 characters-Group of 4 characters-Group of 4 characters-Group of 12 characters. 

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
A standard UUID can also be represented in one of the following ways:
  • Lowercase letters are replaced with uppercase letters. 
    A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11
  • All characters are enclosed by a pair of braces { }. 
    {a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}
  • Some or all hyphens (-) are omitted. 
    a0eebc999c0b4ef8bb6d6bb9bd380a11
  • Hyphens (-) are used to concatenate every four hexadecimal digits. 
    a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11

Statement to enable or disable the UUID-OSSP extension

  • Enable the UUID-OSSP extension. 
    CREATE EXTENSION "uuid-ossp";
  • Disable the UUID-OSSP extension. 
    DROP EXTENSION "uuid-ossp";
Note
You must use a privileged account to install the UUID-OSSP extension.

Functions of the UUID-OSSP extension

  • Functions that return UUIDs
    FunctionDescription
    Function Description
    uuid_generate_v1() This function generates a UUID of version 1. The UUID of version 1 is generated based on the timestamp and the MAC address of a computer.
    Note
    A UUID of version 1 reveals the computer that generated the UUID and the time when the UUID was generated. This type of UUID is not suitable for applications that require high security.
    uuid_generate_v1mc() This function generates a UUID of version 1. This function differs from the uuid_generate_v1() function. 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 UUID of version 3 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 based on the MD5 hashing algorithm. No plaintext can be derived from the generated UUID. The 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. In this case, the UUID can be reproduced.

    uuid_generate_v4() This function generates a UUID of version 4 based on random numbers.
    uuid_generate_v5(namespace uuid, name text) This function generates a UUID of version 5 in the similar manner in which a UUID of version 3 is generated. The difference is that the SHA-1 hashing algorithm is used to generate UUIDs of version 5. This algorithm is more secure than the MD5 hashing algorithm. Therefore, UUIDs of version 5 are recommended.
  • Functions that return UUID constants
    FunctionDescription
    Function Description
    uuid_nil() This function returns a nil UUID constant. This UUID constant is not 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 Uniform Resource Locator (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 UUID of version 1: 
    SELECT uuid_generate_v1();

    The following information is returned:

               uuid_generate_v1
--------------------------------------
 a6808efc-13c8-11ed-ad4f-00163e010e52
(1 row)
  • Execute the following statement to generate a UUID of version 3: 
    SELECT uuid_generate_v3(uuid_ns_url(), 'example.com');

    The following information is returned:

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

    The following information is returned:

               uuid_generate_v4
--------------------------------------
 170d0eb6-520a-4f93-a1b3-89458fffb54c
(1 row)
  • Execute the following statement to generate a UUID of version 5: 
    SELECT uuid_generate_v5(uuid_ns_url(), 'example.com');

    The following information is returned:

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

References