Introduction to PostgreSQL UUID type

UUID stands for Universal Unique Identifier defined byRFC 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-f30f3678f6086ecd8c99-4036-403d-bf84-cf8400f678363f333df6-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 theSERIAL 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()

Thegen_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 willcreate a table whoseprimary key is a UUID data type. Additionally, the values of the primary key column will be generated automatically using thegen_random_uuid() function.

First, create thecontacts 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 thecontact_id column isUUID.

Thecontact_id column has a default value provided by thegen_random_uuid() function, therefore, whenever you insert a new row without specifying the value for the contact_idcolumn, PostgreSQL will call thegen_random_uuid() function to generate the value for it.

Second,insert some data into thecontacts 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')RETURNING*;

Output:

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

The output indicates that thecontact_id column has been populated by the UUID values generated by thegen_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 moduleuuid-ossp that provides specific algorithms to generate UUIDs

To install theuuid-ossp module, you use theCREATE EXTENSION statement as follows:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

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

If you want to generate a UUID value, you can use theuuid_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 theuuid-ossp module documentation.

Summary

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

Last updated onFebruary 1, 2024

On this page

• Introduction to PostgreSQL UUID type
• Generating UUID values
• Creating a table with a UUID column
• Using uuid-ossp module in the old version of PostgreSQL
• Summary