How to Use the PostgreSQL UUID Data Type

Introduction

Universally Unique Identifiers (UUIDs), also known as Globally Unique Identifiers (GUIDs), are 128-bit hexadecimal digits for identifying information in computer systems. All standard UUIDs use 32 characters separated by four hyphens to form an 8-4-4-4-12 sequence illustrated below.

     f136f640-90b7-11ed-a2a0-fd911f8f7f38
     93b5487a-435a-4163-bda4-380504dcef9b

When designing modern database applications, UUIDs help hide sensitive information such as email reset links, document paths, session tokens, and users' asset links. The PostgreSQL server natively supports an uuid data type for storing UUIDs. You must enable the uuid-ossp extension on your PostgreSQL server to generate UUIDs.

The PostgreSQL uuid data type guarantees better uniqueness than the serial (auto-increment integer) data type because it's hard to guess the next UUIDs in a sequence.

This tutorial shows you how to use a managed PostgreSQL database cluster that implements the uuid data type to identify users' records in a table.

Prerequisites

To test this tutorial:

• Set up an Ubuntu 20.04 server.

• Create a non-root sudo user.

• Deploy a managed PostgreSQL database cluster.

• Sign in to your Vultr account. Locate the Products link and navigate to Databases. Click your managed database under Managed Database Name and find your database Connection Details. This guide uses the following sample connection details:

  • username: vultradmin

  • password: EXAMPLE_POSTGRESQL_PASSWORD

  • host: SAMPLE_POSTGRESQL_DB_HOST_STRING.vultrdb.com

  • port: 16751

1. Set Up a Sample Database

Before you begin testing this tutorial, you must connect to your managed PostgreSQL database cluster and set up a sample database by following the steps below:

1. Establish an SSH connection to your Linux server and run the following commands to install the psql command-line tool for PostgreSQL.

    $ sudo apt update 
    $ sudo apt install -y postgresql-client
   

2. Run the psql command below to log in to your managed PostgreSQL server. Enter the correct values after -h, -p, and -U.

    $ psql -h SAMPLE_POSTGRESQL_DB_HOST_STRING.vultrdb.com -p 16751 -U vultradmin defaultdb
   

3. Enter the PostgreSQL database cluster's password when prompted and press Enter to proceed.

    Password for user vultradmin: *********
   

4. Create a sample test_db database.

    defaultdb=> CREATE DATABASE test_db;

   Output.

    CREATE DATABASE
   

5. Connect to the new test_db database.

    defaultdb=> \c test_db;

   Output.

    You are now connected to database "test_db" as user "vultradmin".
   

6. Proceed to the next step to install an extension that enables the UUIDs functionalities in the PostgreSQL server.

2. Install the PostgreSQL uuid-ossp Extension

In the PostgreSQL server, the uuid-ossp extension provides different functions for generating UUIDs using several standard algorithms. Here are some of these uuid-ossp functions:

• uuid_generate_v1 (): This function generates version 1 (v1) UUIDs that consider your server's timestamp and Media Access Control (MAC) address. The final UUIDs generated by this function are time and host-based. The v1 function guarantees a unique UUID every time you run it. However, the v1 function is more prone to attacks because attackers can retrieve the host MAC address and timestamp from a sample UUID and predict future UUIDs. Therefore, avoid using the v1 function when coding authorization systems.

• uuid_generate_v4 (): This function generates version 4 (v4) UUIDs using a random algorithm making the final UUIDs 100% anonymous. The only drawback of the v4 function is a low probability of collision or duplication.

To see the above uuid-ossp functions in action, follow the steps below:

1. Install the PostgreSQL uuid-ossp extension.

    test_db=> CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

   Output.

    CREATE EXTENSION
   

2. Ensure PostgreSQL shows the uuid-ossp extension in the list of installed extensions by running either of the commands below:

   • Option 1:

       test_db=> SELECT
                     * 
                 FROM pg_extension;
     

   • Option 2:

       test_db=> \dx

3. Verify the following outputs. PostgreSQL now lists uuid-ossp under the list of installed extensions.

   • Output 1:

         oid  |  extname  | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
       -------+-----------+----------+--------------+----------------+------------+-----------+--------------
        14748 | plpgsql   |       10 |           11 | f              | 1.0        |           |
        16415 | uuid-ossp |       10 |         2200 | t              | 1.1        |           |
       (2 rows)
     

   • Output 2:

                               List of installed extensions
          Name    | Version |   Schema   |                   Description
       -----------+---------+------------+-------------------------------------------------
        plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
        uuid-ossp | 1.1     | public     | generate universally unique identifiers (UUIDs)
       (2 rows)

4. Test the uuid-ossp functions.

   • Generate a v1 UUID:

       test_db=> SELECT uuid_generate_v1();

     Output.

                  uuid_generate_v1
       --------------------------------------
        38a6117e-90c6-11ed-a2a0-fd911f8f7f38
       (1 row)
     

   • Generate a v4 UUID:

       test_db=> SELECT uuid_generate_v4();

     Output.

                  uuid_generate_v4
       --------------------------------------
        c24740c5-a357-4a9d-bc6b-f57ba62c290d
       (1 row)

5. Follow the next step to learn how to define a table column that stores UUIDs.

3. Define an uuid Column In a PostgreSQL Table

You're now familiar with how PostgreSQL generates UUIDs. Put this knowledge into practice by defining a table that uses the uuid data type by following the steps below:

1. Create a system_users table. The user_id is the PRIMARY KEY for the system_users table and uses the PostgreSQL uuid data type. Use the DEFAULT uuid_generate_v4() statement to instruct PostgreSQL to automatically generate new UUIDs every time you insert a record into the system_users table. Then, define the username, first_name, and last_name columns using the VARCHAR(50) data type.

    test_db=> CREATE TABLE system_users (
                  user_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
                  username   VARCHAR(50),
                  first_name VARCHAR(50),
                  last_name  VARCHAR(50)                                 
              );

   Output.

    CREATE TABLE
   

2. Insert sample users into the system_users table.

    test_db=> INSERT INTO system_users (username, first_name, last_name) VALUES ('JOHN', 'DOE', 'john_doe');
              INSERT INTO system_users (username, first_name, last_name) VALUES ('JANE', 'ANN', 'jane_ann');
              INSERT INTO system_users (username, first_name, last_name) VALUES ('PETER', 'HENRY', 'peter_henry');

   Output.

    ...
    INSERT 0 1
   

3. Query the system_users table to ensure the uuid_generate_v4() function runs as expected.

    test_db=> SELECT
                  user_id,
                  username,
                  first_name,
                  last_name                                 
              FROM system_users;
   

4. Verify the output below. As you can see, PostgreSQL generates unique UUIDs when creating records in the table.

                   user_id                | username | first_name |  last_name
    --------------------------------------+----------+------------+-------------
     df31f9f2-b890-4647-80f2-51eae1f2753d | JOHN     | DOE        | john_doe
     2be0b94f-a543-4c37-859c-b3ad1aab8b5e | JANE     | ANN        | jane_ann
     48757fba-11b9-437c-8293-80ccc32f40b8 | PETER    | HENRY      | peter_henry
    (3 rows)
   

5. Learn the importance of UUIDs in the next step.

4. Understand the Importance of UUIDs in Distributed Databases

Unlike the PostgreSQL serial data type that uses a sequence of known integers, the uuid data type offers a superior alternative when working with distributed databases. To put this in a better perspective, assume company ABC acquires company XYZ and both companies have a separate list of users stored in a system_users table. In that scenario, if both companies use UUIDs as primary keys, there is a guarantee that the user_ids in both databases are unique.

Also, the UUIDs functions allow separate PostgreSQL nodes to generate globally unique primary keys autonomously without having a central node for issuing the UUIDs. Therefore, if your PostgreSQL application relies on distributed workloads from different unconnected nodes, consider using the uuid data type instead of the serial data type.

Conclusion

This tutorial shows you how to implement the PostgreSQL uuid data type to store UUIDs generated using the uuid-ossp extension. Use the sample codes in this tutorial to generate UUIDs across decentralized PostgreSQL nodes without worrying about collisions.

For more information about Vultr's PostgreSQL-managed databases, check out the links below:

• Vultr Managed Databases Quickstart

• How to Import CSV Data to Vultr Managed Databases for PostgreSQL

• How to Use Vultr Managed Databases for PostgreSQL with NodeJS