PostgreSQL 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 a 128-bit quantity generated by an algorithm that makes it unique in the known universe using the same algorithm.

The following shows some examples of UUID values:

40e6215d-b5c6-4896-987c-f30f3678f608
6ecd8c99-4036-403d-bf84-cf8400f67836
3f333df6-90a4-4fda-8dd3-9485d27cee36Code language: SQL (Structured Query Language) (sql)

A UUID is a sequence of 32 digits of hexadecimal digits represented in groups separated by hyphens.

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

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

Generating UUID values

PostgreSQL provides you with a function to generate a UUID:

gen_random_uuid()

The gen_random_uuid() function returns a version 4 (random) UUID. For example:

SELECT gen_random_uuid();

Output:

           gen_random_uuid
--------------------------------------
 d6eb621f-6dd0-4cdc-93f5-07f51b249b51
(1 row)

Creating a table with a UUID column

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

First, create the contacts table:

CREATE TABLE contacts (
    contact_id uuid DEFAULT gen_random_uuid(),
    first_name VARCHAR NOT NULL,
    last_name VARCHAR NOT NULL,
    email VARCHAR NOT NULL,
    phone VARCHAR,
    PRIMARY KEY (contact_id)
);Code language: SQL (Structured Query Language) (sql)

In this statement, the data type of the contact_id column is UUID.

The contact_id column has a default value provided by the gen_random_uuid() function, therefore, whenever you insert a new row without specifying the value for the contact_id column, PostgreSQL will call the gen_random_uuid() 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', '[email protected]',  '408-237-2345'), 
  ('Jane', 'Smith', '[email protected]', '408-237-2344'), 
  ('Alex', 'Smith', '[email protected]', '408-237-2343')
RETURNING *;Code language: SQL (Structured Query Language) (sql)

Output:

              contact_id              | first_name | last_name |         email          |    phone
--------------------------------------+------------+-----------+------------------------+--------------
 ca61da8c-938a-48a6-8eb6-55aa08cd1b08 | John       | Smith     | [email protected] | 408-237-2345
 fe2af584-8576-4d0e-b10d-6ec970732f8e | Jane       | Smith     | [email protected] | 408-237-2344
 141aefe8-f553-43b9-bfbf-91361e83b15e | Alex       | Smith     | [email protected] | 408-237-2343
(3 rows)

The output indicates that the contact_id column has been populated by the UUID values generated by the gen_random_uuid() function.

Using uuid-ossp module in the old version of PostgreSQL

If you use an old version of PostgreSQL, you need to use a third-party module uuid-ossp that provides specific algorithms to generate UUIDs

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

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";Code language: SQL (Structured Query Language) (sql)

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

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

SELECT uuid_generate_v4();

Output:

           uuid_generate_v4
--------------------------------------
 351c1afe-21b2-486c-951b-66bc9e852530
(1 row)Code language: SQL (Structured Query Language) (sql)

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

Summary

• UUID stands for Universal Unique Identifier.
• Use the gen_random_uuid() function to generate a version 4 (random) UUID.