Table of Contents
Was this article helpful?
Try Vultr Today with

$50 Free on Us!

Want to contribute?

You could earn up to $600 by adding new articles.

How to Use the PostgreSQL UUID Data Type

Author: Francis Ndungu

Last Updated: Mon, Jan 23, 2023
PostgreSQL Programming


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.



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.


To test this tutorial:

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 -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;


  5. Connect to the new test_db database.

    defaultdb=> \c test_db;


    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";


  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();


      (1 row)
    • Generate a v4 UUID:

      test_db=> SELECT uuid_generate_v4();


      (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)                                 


  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');


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

    test_db=> SELECT
              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.


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:

Want to contribute?

You could earn up to $600 by adding new articles.