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.

Create a free Postgres Database in 0.3 seconds on Neon. Ship faster with database branching. Handle peaks efficiently with autoscaling.

Sponsored

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.
Was this tutorial helpful ?