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

Installation

Execute the following statement to install the uuid-ossp extension:

CREATE EXTENSION "uuid-ossp";
Note You can only install the uuid-ossp extension as the superuser or rds_superuser user.

Overview

After you install the uuid-ossp extension, the system automatically creates the UUID data type and supports B-tree indexes.

mydb=> create extension "uuid-ossp";
CREATE EXTENSION
mydb=> \dT
     List of data types
 Schema | Name | Description
--------+------+-------------
 public | uuid |
(1 row)

The UUID data type is used to store universally unique identifiers (UUIDs) defined by standards such as RFC 4122 and ISO/IEF 9834-8:2005. UUIDs are also known as globally unique identifiers (GUIDs) in some systems. No identical UUIDs can be generated by the same algorithm or any other methods. In a distributed database system, UUIDs ensure uniqueness better than sequences, because sequences can only ensure uniqueness in a single database.

A standard UUID is a 128-bit hexadecimal sequence in lowercase. It consists of 32 hexadecimal digits displayed in five groups that are separated with hyphens (-). These five groups consist of one 8-digit group, three 4-digit groups, and one 12-digit group. Example:

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

In addition to standard UUIDs, the uuid-ossp extension supports UUIDs in other formats such as UUIDs written in uppercase, UUIDs with some or all hyphens (-) deleted, UUIDs in which every four digits are separated with a hyphen (-), and UUIDs enclosed in a pair of braces ({}). 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 None of the available versions of AnalyticDB for PostgreSQL allow you to choose a column of the UUID data type as the distribution key.

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 reveals the identity of the computer that created the identifier and the time when the creation occurred. Therefore, this type of UUID is unsuitable for certain security-sensitive applications.
    uuid_generate_v1mc() This function generates a version 1 UUID. It differs from the uuid_generate_v1() function in the following aspect: 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 given namespace by using the specified name.
    • The namespace must be one of the special constants that are generated by the uuid_ns_*() function 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(), 'http://www.postgresql.org');

    The name parameter is MD5-hashed. Therefore, no plaintext can be derived from the generated UUID. A UUID generated by using this function does not require a random algorithm or depend on any environment elements related to system running and is therefore reproducible.

    uuid_generate_v4() This function generates a version 4 UUID, which is derived entirely from random numbers.
    uuid_generate_v5(namespace uuid, name text) This function generates a version 5 UUID, which works like a version 3 UUID except that SHA-1 is used as a hashing method. Version 5 is preferred over version 3 because SHA-1 is considered to be securer than MD5.
  • Functions returning UUID constants
    Function Description
    uuid_nil() This function generates a nil UUID constant, which is not considered as a real UUID.
    uuid_ns_dns() This function generates a constant that designates the DNS namespace for UUIDs.
    uuid_ns_url() This function generates a constant that designates the URL namespace for UUIDs.
    uuid_ns_oid() This function generates 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 generates a constant that designates the X.500 distinguished name (DN) namespace for UUIDs.

Examples

mydb=# create extension "uuid-ossp";
CREATE EXTENSION
mydb=# SELECT uuid_generate_v1();
           uuid_generate_v1
--------------------------------------
 c7f83ba4-bd93-11e9-8674-40a8f01ec4e8
(1 row)

mydb=# SELECT uuid_generate_v3(uuid_ns_url(), 'http://www.postgresql.org');
           uuid_generate_v3
--------------------------------------
 cf16fe52-3365-3a1f-8572-288d8d2aaa46
(1 row)

mydb=# SELECT uuid_generate_v4();
           uuid_generate_v4
--------------------------------------
 d7a8d47e-58e3-4bd9-9340-8553ac03d144
(1 row)

mydb=# SELECT uuid_generate_v5(uuid_ns_url(), 'http://www.postgresql.org');
           uuid_generate_v5
--------------------------------------
 e1ee1ad4-cd4e-5889-962a-4f605a68d94e
(1 row)

mydb=# create table x(id uuid, value float4) distributed by (value);
CREATE TABLE
mydb=# insert into x select uuid_generate_v4(),(r*random()) from generate_series(1,100000)r;
INSERT 0 100000
mydb=# CREATE INDEX idx_x_id ON x USING btree (id);
CREATE INDEX
mydb=# ANALYZE x;
ANALYZE
mydb=# explain SELECT count(1) from x where id = '9f830fc9-a1ee-425f-844b-9c73290a91ad';
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Aggregate  (cost=200.44..200.45 rows=1 width=8)
   ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=200.37..200.42 rows=1 width=8)
         ->  Aggregate  (cost=200.37..200.38 rows=1 width=8)
               ->  Index Scan using idx_x_id on x  (cost=0.00..200.37 rows=1 width=0)
                     Index Cond: id = '9f830fc9-a1ee-425f-844b-9c73290a91ad'::uuid
 Settings:  enable_seqscan=off; optimizer=off
 Optimizer status: legacy query optimizer
(7 rows)

References