Generating Universally Unique Identifiers in PostgreSQL

In this tutorial, we will cover the basics of generating universally unique identifiers (UUIDs) in PostgreSQL. UUIDs are 128-bit numbers used to identify information in computer systems, and they are commonly used as primary keys in databases.

Introduction to UUIDs

There are several versions of UUIDs, but the most commonly used is Version 4, which generates a random 128-bit number. This version is suitable for most applications, including generating unique identifiers for rows in a database table.

Installing the uuid-ossp Extension

To generate UUIDs in PostgreSQL, you need to install the uuid-ossp extension. This extension provides functions for generating UUIDs, including uuid_generate_v4().

If you are using PostgreSQL 9.1 or later, you can install the uuid-ossp extension by running the following SQL command:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

If you are using an earlier version of PostgreSQL, you will need to find and run a script in a .sql file to load the extension.

Generating UUIDs

Once the uuid-ossp extension is installed, you can generate a UUID using the uuid_generate_v4() function. Here’s an example:

SELECT uuid_generate_v4();

This will return a random Version 4 UUID.

You can also use the gen_random_uuid() function from the pgcrypto module to generate a UUID. To use this function, you need to install the pgcrypto extension by running the following SQL command:

CREATE EXTENSION "pgcrypto";

Then, you can generate a UUID using the following query:

SELECT gen_random_uuid();

Using UUIDs as Primary Keys

To use a UUID as a primary key in a table, you need to define a column with the uuid data type and set it as the default value for that column. Here’s an example:

CREATE TABLE items (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255),
    price DECIMAL(10, 2)
);

In this example, the id column is defined with the uuid data type and set as the primary key. The default value for this column is generated using the uuid_generate_v4() function.

Inserting Rows with UUIDs

To insert a row into a table with a UUID primary key, you can use the following query:

INSERT INTO items (name, price) VALUES ('Item 1', 10.99);

In this example, the id column is not specified in the INSERT statement because it has a default value generated by the uuid_generate_v4() function.

PostgreSQL 13 and Later

As of PostgreSQL 13, you can use the gen_random_uuid() function natively without installing any extensions. This function generates a random Version 4 UUID.

Here’s an example:

SELECT gen_random_uuid();

This will return a random Version 4 UUID.

Conclusion

In this tutorial, we covered the basics of generating universally unique identifiers (UUIDs) in PostgreSQL. We discussed how to install the uuid-ossp extension and use its functions to generate UUIDs. We also covered how to define a column with the uuid data type and set it as the default value for that column.

By following this tutorial, you should be able to generate UUIDs in your PostgreSQL database and use them as primary keys in your tables.