How to Generate UUIDs in PostgreSQL

Generating unique identifiers is a common requirement in database applications. In PostgreSQL, the universally unique identifier (UUID) data type provides a straightforward way to accomplish this. In this article, we’ll explore how to generate UUIDs in PostgreSQL, discuss why and when to use them, and show code examples with expected output.

What is a UUID?

UUID stands for Universally Unique Identifier, a 128-bit number used to uniquely identify information in computer systems. The key advantage of using UUIDs is their ability to provide a unique key across different platforms and databases without coordination.

Why Use UUIDs?

• Uniqueness Across Systems: Unlike traditional serial IDs, UUIDs can be generated independently across different systems.
• Hard to Guess: UUIDs are not sequential, making them less predictable.
• Globally Unique: They ensure global uniqueness, preventing ID collisions when merging datasets.

Generating UUIDs in PostgreSQL

PostgreSQL makes it easy to generate UUIDs with its built-in support, but it requires additional extensions to be enabled.

Step 1: Enable the uuid-ossp Extension

The uuid-ossp extension provides functions to generate UUIDs. You must enable this extension in your database.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Step 2: Using uuid_generate_v4()

The most commonly used function for generating random UUIDs is uuid_generate_v4(). Here’s how to use it:

SELECT uuid_generate_v4() as generated_uuid;

Expected Output:

          generated_uuid          
--------------------------------------
 550e8400-e29b-41d4-a716-446655440000

Each execution of the function returns a new, unique UUID.

Step 3: Using UUIDs in Tables

You can use UUIDs as primary keys or unique identifiers in your tables. It’s good practice to set the column type to UUID and provide a default using the uuid_generate_v4() function.

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    username VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL
);

Inserting Data:

You can rely on PostgreSQL to automatically generate UUIDs when inserting new records.

INSERT INTO users (username, email) VALUES ('johndoe', 'john@example.com');

SELECT id, username, email FROM users;

Expected Output:

                  id                | username |       email       
-----------------------------------+----------+-------------------
 8df00d7f-6d5d-45e8-bd9c-8c5f29e6e0a7 | johndoe  | john@example.com

Alternative UUID Generation Methods

uuid_generate_v1()

Generates a UUID based on the current timestamp and MAC address. This is deterministic and less secure but can be useful in some scenarios.

SELECT uuid_generate_v1() as generated_uuid;

Expected Output:

          generated_uuid          
--------------------------------------
 6a7fede0-e1e3-11ed-b5ea-0242ac120002

uuid_generate_v1mc()

Similar to uuid_generate_v1(), but allows specifying a MAC address.

uuid_generate_v3(namespace UUID, name TEXT)

Generates a version 3 UUID based on a namespace and a name, using MD5 hashing.

uuid_generate_v5(namespace UUID, name TEXT)

Similar to uuid_generate_v3(), but uses SHA-1 hashing, creating a version 5 UUID.

Conclusion

PostgreSQL’s UUID functionality provides a robust and flexible way to generate unique identifiers for your database applications. By leveraging UUIDs, you can ensure that your IDs are unique, non-predictable, and consistent across different systems. Whether you’re creating a new application or migrating an existing one, UUIDs offer a modern approach to ID generation.

With the uuid-ossp extension and its variety of functions, PostgreSQL makes it easy to choose the type of UUID that suits your needs. Start using UUIDs in your database today and benefit from their globally unique capabilities.