PostgreSQL - UUID Data Type

The UUID (Universally Unique Identifier) is a 128-bit identifier defined by RFC 4122. It generates globally unique values using algorithms that ensure no duplication, making it ideal for distributed systems. PostgreSQL supports UUID as a data type and provides extensions for UUID generation, which is particularly useful in multi-database applications or distributed systems where unique identifiers are crucial.

In this article, we will explain the PostgreSQL UUID Data Type along with its syntax, examples, and usage scenarios. This guide will help us understand how to effectively implement and manage UUIDs in our PostgreSQL databases.

Why Use UUIDs in PostgreSQL?

UUIDs offer a key advantage over the SERIAL data type by ensuring uniqueness not only within a single database but across multiple databases or systems. This makes UUIDs an optimal choice for applications that require global uniqueness. UUIDs are commonly used in distributed systems and microservices architectures, where identifiers must be unique even across network boundaries.

Installing the UUID Extension in PostgreSQL

While PostgreSQL allows storing and comparing UUID values, it does not include built-in functions for generating them. Instead, it relies on third-party modules, such as the 'uuid-ossp' module, which implements standard algorithms for UUID generation.

To install the uuid-ossp extension, execute the following command:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Generating UUIDs in PostgreSQL

The uuid-ossp module provides several functions to generate UUIDs based on different algorithms. These functions make it easy to create UUIDs directly within PostgreSQL, supporting various use cases and levels of randomness for unique key generation.

1. UUID Version 1

UUID version 1 combines the computer’s MAC address, the current timestamp, and a random value to produce a unique identifier. Use the 'uuid_generate_v1()' function as shown below:

Query:

SELECT uuid_generate_v1();

Output

2. UUID Version 4

UUID version 4 generates a UUID based entirely on random numbers, ensuring a high degree of uniqueness. Use the 'uuid_generate_v4()' function as follows:

Query:

SELECT uuid_generate_v4();

Output

Example of PostgreSQL UUID Data Type

Let us take a look at an example of UUID Data Type in PostgreSQL to better understand the concept. In this example we will make a table whose primary key is a UUID data type. In supplement, the values of the primary key column will be produced automatically through the 'uuid_generate_v4()' function.

Query:

CREATE TABLE contacts (
    contact_id uuid DEFAULT uuid_generate_v4 (),
    first_name VARCHAR NOT NULL,
    last_name VARCHAR NOT NULL,
    email VARCHAR NOT NULL,
    phone VARCHAR,
    PRIMARY KEY (contact_id)
);

INSERT INTO contacts (first_name, last_name, email, phone)
VALUES
    ('Raju', 'Kumar', '[email protected]', '408-237-2345'),
    ('Nikhil', 'Aggarwal', '[email protected]', '408-237-2344'),
    ('Anshul', 'Aggarwal', '[email protected]', '408-237-2343'
);

SELECT * FROM contacts;

Output

Important Points About PostgreSQL UUID Data Type

• UUID versions: PostgreSQL supports multiple versions of UUIDs (v1, v4, etc.), each using different algorithms for generation.
  
• Extensions for UUIDs: PostgreSQL’s core does not natively generate UUIDs; it relies on extensions like 'uuid-ossp' or 'pgcrypto'.
  
• UUID format: UUIDs are represented in a standard textual format (8-4-4-4-12 hexadecimal digits) which can be easily read and parsed by humans and systems.
  
• Client-side vs. server-side generation: UUIDs can be generated on the client-side (in application code) or server-side (in the database). Client-side generation can reduce the load on the database.

Conclusion

PostgreSQL's support for the UUID data type provides a reliable method for unique data identification across distributed systems and databases. By utilizing the uuid-ossp extension, PostgreSQL allows the generation of UUIDs with standard functions, enhancing the flexibility and security of our database designs. Using UUIDs also helps prevent conflicts across databases, making them ideal for scalable and distributed environments.