A Complete Guide to PostgreSQL UUID Data Type

In this tutorial, we will learn about the UUID data type in PostgreSQL. It is one of the easiest and most useful data types while developing any application. So, read the tutorial carefully and try it yourself. Now without further ado, let’s get started!

Also Read: PostgreSQL – Data Types

What is UUID Type in PostgreSQL?

The UUID stands for Universal Unique Identifier defined by RFC 4122. The UUID Type stores the UUID values of size 128 bit. Note that, some other systems also refer to UUID as a globally unique identifier or GUID.

As the name suggests, it is a globally unique id generated by an algorithm that is unlikely to be repeated in the known universe by the same algorithm. Therefore, the UUIDs are used mostly in distributed systems where uniqueness matters.

UUID is a sequence of lower-case hexadecimal digits written in multiple groups separated by hyphens. The first group contains 8 digits followed by three groups of 4 digits followed by a group of 12 digits. This makes a 32-digit long string representing 128 bits.

The format of UUID in almost all the database systems is the same, which looks like this:

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

However, PostgreSQL also accepts the following formats for the input:

A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11
{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}
a0eebc999c0b4ef8bb6d6bb9bd380a11
a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11
{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}

Note that, the output is still in the standard format.

How to Generate a UUID Value

Starting from PostgreSQL version 13, we get the prebuilt function to generate a UUID as shown below.

gen_random_uuid()

This function returns the version 4 UUID which is highly recommended to use in your applications.

Let’s try generating a UUID.

SELECT gen_random_uuid();

Here, the generated UUID is totally random.

Note that, prior to the mentioned version, there was no function available in PostgreSQL to generate a UUID. Therefore, you had to install a third-party module that provides a way to generate a UUID.

uuid-ossp to Generate a UUID Value

The module that PostgreSQL recommend for generating UUID is uuid-ossp.

To use this module, you have to install it first. Let’s see how.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The uuid-ossp module provides a few functions to generate a UUID as shown below:

uuid_generate_v1 () → uuid
uuid_generate_v1mc () → uuid
uuid_generate_v3 ( namespace uuid, name text ) → uuid
uuid_generate_v4 () → uuid
uuid_generate_v5 ( namespace uuid, name text ) → uuidCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here is the list of available functions to generate a UUID using the uuid-ossp module. You can read each one’s description here.

The uuid_generate_v4() also generates a version 4 uuid which is totally random and equivalent to the gen_random_uuid() function.

Let’s try generating an uuid using the uuid_generate_v4.

SELECT uuid_generate_v4();

Now let’s create a table with the UUID type column.

Create a Table With UUID Column

Now let’s create a table having a uuid data type column.

CREATE TABLE songs (
   id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
   name VARCHAR(50),
   description TEXT,
   created_at TIMESTAMP DEFAULT now()
)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the id column has the uuid datatype to which we set the default value. The default value will be generated by the gen_random_uuid() function which will generate a random uuid every time the record is saved into the table.

Also, the same column is set as a primary key because there is close to no possibility of duplication.

Now let’s insert some records in it.

INSERT INTO songs (name, description)
VALUES ('Bohemian Rhapsody', 'A classic rock ballad by Queen'),
       ('Shape of You', 'A popular pop song by Ed Sheeran'),
       ('Thunderstruck', 'A hit rock song by AC/DC');

SELECT * FROM songs;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

As you can see, the table contains valid UUIDs which are generated using the prebuilt function.

If you are using the PostgreSQL version older than 13, you can install the uuid_ossp module and use the uuid_generate_v4() function to generate the random UUIDs.

Conclusion

In this tutorial, we learned to use the UUID data type in PostgreSQL as well as to generate a UUID using two ways. We recommend you upgrade your PostgreSQL version to the latest after which you won’t need to install an extension. So that, you can take advantage of an out-of-the-box feature of PostgreSQL.

References

PostgreSQL official documentation on UUID type.

PostgreSQL official documentation on UUID functions.