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-9485d27cee36

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)

You can try the gen_random_uuid() function using the interactive SQL editor below. Running the query will provision a Postgres database on Neon, and you can execute the query to see the output.

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

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]](../cdn-cgi/l/email-protection.html)',  '408-237-2345'),
  ('Jane', 'Smith', '[[email protected]](../cdn-cgi/l/email-protection.html)', '408-237-2344'),
  ('Alex', 'Smith', '[[email protected]](../cdn-cgi/l/email-protection.html)', '408-237-2343')
RETURNING *;

Output:

contact_id              | first_name | last_name |         email          |    phone
--------------------------------------+------------+-----------+------------------------+--------------
 ca61da8c-938a-48a6-8eb6-55aa08cd1b08 | John       | Smith     | [[email protected]](../cdn-cgi/l/email-protection.html) | 408-237-2345
 fe2af584-8576-4d0e-b10d-6ec970732f8e | Jane       | Smith     | [[email protected]](../cdn-cgi/l/email-protection.html) | 408-237-2344
 141aefe8-f553-43b9-bfbf-91361e83b15e | Alex       | Smith     | [[email protected]](../cdn-cgi/l/email-protection.html) | 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";

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)

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.