PostgreSQL UUID Data Type

Summary: in this tutorial, you’ll learn how to store UUID values using the PostgreSQL UUID type.

Getting Started with PostgreSQL UUID type #

UUID stands for universal unique identifier and is a standard defined by the RFC 4122.

A UUID is 128 bits long and does not require central registration. It guarantees uniqueness across systems and time. PostgreSQL uses the UUID type for storing UUID values.

Here’s the syntax for defining a UUID column:

CREATE TABLE table_name (
   column_name UUID,
   ...
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Typically, we use the UUID as the type of a primary key column of the table:

CREATE TABLE table_name (
   column_name UUID PRIMARY KEY,
   ...
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The reason is that UUID is unique and does not expose the internal sequence to the public.

For example, if you have a customer with ID 10000, it may expose that your system has 10K customers.

Your competitor may use this information to gain a competitive advantage when they know the number of customers you have.

Using UUID as the primary key, both PostgreSQL and your application can generate it while maintaining uniqueness.

For example, you can generate a key (UUID) to insert a new customer and use it to create a customer profile.

// Generate UUID for a customer
// Use UUID to create a customer profileCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If you use the identity column, you have to insert a new customer first, get the generated ID by the PostgreSQL, and use that ID to create a customer profile.

Function to generate UUID values #

PostgreSQL provides the gen_random_uuid() that allows you to generate a UUID value. For example, the following statement generates a UUID value:

SELECT gen_random_uuid();Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

           gen_random_uuid
--------------------------------------
 68f375c8-1b55-4558-a176-72a38a520149Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Each time you execute the function, it generates a new UUID value.

You can use the gen_random_uuid() function to generate default values for the UUID primary key column:

CREATE TABLE table_name(
   user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
   ...
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL UUID data type example #

First, create a table called customers that uses the UUID type as the primary key column:

CREATE TABLE customers (
  customer_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In the customers table, the customer_id column is a primary key column. The type of the customer_id column is UUID.

When you insert a row into the table, PostgreSQL automatically generates UUID values for the customer_id column by calling the gen_random_uuid() function.

Second, insert data into the customers table:

INSERT INTO
  customers (name)
VALUES
  ('ABC Corp.'),
  ('XYZ Inc.') 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

             customer_id              |   name
--------------------------------------+-----------
 f1af387f-ac03-4784-bbcb-b564a88cd682 | ABC Corp.
 aae1c410-06ff-48df-818b-dbed92a1efbc | XYZ Inc.Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use UUID data type for a primary key column of a table.
• Use the gen_random_uuid() function to generate UUID values.

Quiz #