PostgreSQL Tutorial: UUID Data Type

Summary: in this tutorial, you will learn about the PostgreSQL UUID data type and how to generate UUID values using a supplied module.

Introduction to PostgreSQL UUID type

UUID stands for Universal Unique Identifier defined by RFC 4122 and other related standards. A UUID value is 128-bit quantity generated by an algorithm that make it unique in the known universe using the same algorithm. The following shows some examples of the UUID values:

40e6215d-b5c6-4896-987c-f30f3678f608
6ecd8c99-4036-403d-bf84-cf8400f67836
3f333df6-90a4-4fda-8dd3-9485d27cee36

As you can see, a UUID is a sequence of 32 digits of hexadecimal digits represented in groups separated by hyphens.

Because of its uniqueness feature, you often found UUID in the distributed systems because it guarantees a better uniqueness than the SERIAL data type which generates only unique values within a single database.

To stores UUID values in the PostgreSQL database, you use the UUID data type.

Generating UUID values

PostgreSQL allows you store and compare UUID values but it does not include functions for generating the UUID values in its core.

Instead, it relies on the third-party modules that provide specific algorithms to generate UUIDs. For example the uuid-ossp module provides some handy functions that implement standard algorithms for generating UUIDs.

To install the uuid-ossp module, you use the CREATE EXTENSION statement as follows:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

The IF NOT EXISTS clause allows you to avoid re-installing the module.

To generate the UUID values based on the combination of computer’s MAC address, current timestamp, and a random value, you use the uuid_generate_v1() function:

SELECT uuid_generate_v1();

The function generated the following a UUID value:

           uuid_generate_v1
--------------------------------------
 0e37df36-f698-11e6-8dd4-cb9ced3df976
(1 row)

If you want to generate a UUID value solely based on random numbers, you can use the uuid_generate_v4() function. For example:

SELECT uuid_generate_v4();

           uuid_generate_v4
--------------------------------------
 a81bc81b-dead-4e5d-abff-90865d1e13b1
(1 row)

For more information on the functions for UUID generation, check it out the uuid-ossp module documentation.

Creating a table with UUID column

We will create a table whose primary key is UUID data type. In addition, the values of the primary key column will be generated automatically using the uuid_generate_v4() function.

First, create the contacts table using the following statement:

CREATE TABLE contacts (
    contact_id uuid DEFAULT uuid_generate_v4 (),
    first_name VARCHAR NOT NULL,
    last_name VARCHAR NOT NULL,
    email VARCHAR NOT NULL,
    phone VARCHAR,
    PRIMARY KEY (contact_id)
);

In this statement, the data type of the contact_id column is UUID. The contact_id column has a default values provided by the uuid_generate_v4() function, therefore, whenever you insert new row without specifying the value for the contact_id column, PostgreSQL will call the uuid_generate_v4() function to generate the value for it.

Second, insert some data into the contacts table:

INSERT INTO contacts (
    first_name,
    last_name,
    email,
    phone
)
VALUES
    (
        'John',
        'Smith',
        'john.smith@example.com',
        '408-237-2345'
    ),
    (
        'Jane',
        'Smith',
        'jane.smith@example.com',
        '408-237-2344'
    ),
    (
        'Alex',
        'Smith',
        'alex.smith@example.com',
        '408-237-2343'
    );

Third, query all rows in the contacts table using the following SELECT statement:

SELECT
    *
FROM
    contacts;

As you can see the contact_id column has been populated by the UUID values generated by the uuid_generate_v4() function.

In this tutorial, you have learned how to use PostgreSQL UUID data type and how to generate UUID values using the uuid-ossp module.