In Neon, you can recover a 100 TB database in 1 second. No more fear of breaking production

PostgreSQL UUID Data Type

Summary: in this tutorial, you will learn about the PostgreSQL UUID data type and how to generate UUID values using a supplied module.

Introduction to PostgreSQL UUID type

UUID stands for Universal Unique Identifier defined by RFC 4122 and other related standards.

A UUID value is a 128-bit quantity generated by an algorithm that makes it unique in the known universe using the same algorithm.

The following shows some examples of UUID values:

40e6215d-b5c6-4896-987c-f30f3678f608
6ecd8c99-4036-403d-bf84-cf8400f67836
3f333df6-90a4-4fda-8dd3-9485d27cee36

A UUID is a sequence of 32 digits of hexadecimal digits represented in groups separated by hyphens.

Because of its uniqueness feature, you often find UUID in distributed systems because it guarantees a better uniqueness than the SERIAL data type which generates unique values within a single database.

To store UUID values in the PostgreSQL database, you use the UUID data type.

Generating UUID values

PostgreSQL provides you with a function to generate a UUID:

gen_random_uuid()

The gen_random_uuid() function returns a version 4 (random) UUID. For example:

SELECT gen_random_uuid();

Output:

gen_random_uuid
--------------------------------------
 d6eb621f-6dd0-4cdc-93f5-07f51b249b51
(1 row)

You can try the gen_random_uuid() function using the interactive SQL editor below. Running the query will provision a Postgres database on Neon, and you can execute the query to see the output.

      Creating a table with a UUID column

      We will create a table whose primary key is a UUID data type. Additionally, the values of the primary key column will be generated automatically using the gen_random_uuid() function.

      First, create the contacts table:

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

      In this statement, the data type of the contact_id column is UUID.

      The contact_id column has a default value provided by the gen_random_uuid() function, therefore, whenever you insert a new row without specifying the value for the contact_id column, PostgreSQL will call the gen_random_uuid() function to generate the value for it.

      Second, insert some data into the contacts table:

      INSERT INTO contacts ( first_name, last_name, email, phone)
      VALUES
        ('John', 'Smith', '[[email protected]](../cdn-cgi/l/email-protection.html)',  '408-237-2345'),
        ('Jane', 'Smith', '[[email protected]](../cdn-cgi/l/email-protection.html)', '408-237-2344'),
        ('Alex', 'Smith', '[[email protected]](../cdn-cgi/l/email-protection.html)', '408-237-2343')
      RETURNING *;

      Output:

      contact_id              | first_name | last_name |         email          |    phone
      --------------------------------------+------------+-----------+------------------------+--------------
       ca61da8c-938a-48a6-8eb6-55aa08cd1b08 | John       | Smith     | [[email protected]](../cdn-cgi/l/email-protection.html) | 408-237-2345
       fe2af584-8576-4d0e-b10d-6ec970732f8e | Jane       | Smith     | [[email protected]](../cdn-cgi/l/email-protection.html) | 408-237-2344
       141aefe8-f553-43b9-bfbf-91361e83b15e | Alex       | Smith     | [[email protected]](../cdn-cgi/l/email-protection.html) | 408-237-2343
      (3 rows)

      The output indicates that the contact_id column has been populated by the UUID values generated by the gen_random_uuid() function.

      Using uuid-ossp module in the old version of PostgreSQL

      If you use an old version of PostgreSQL, you need to use a third-party module uuid-ossp that provides specific algorithms to generate UUIDs

      To install the uuid-ossp module, you use the CREATE EXTENSION statement as follows:

      CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

      The IF NOT EXISTS clause allows you to avoid re-installing the module.

      If you want to generate a UUID value, you can use the uuid_generate_v4() function. For example:

      SELECT uuid_generate_v4();

      Output:

      uuid_generate_v4
      --------------------------------------
       351c1afe-21b2-486c-951b-66bc9e852530
      (1 row)

      For more information on the functions for UUID generation, check out the uuid-ossp module documentation.

      Summary

      • UUID stands for Universal Unique Identifier.
      • Use the gen_random_uuid() function to generate a version 4 (random) UUID.

      Last updated on

      Was this page helpful?