PostgreSQL database management system provides many data types like string, integer, character, etc. to store the data in the tables. One such data type is the UUID which is easier and more useful as it can be used to store randomly generated values while creating an application. It is unlikely that the UUID-generated numbers can repeat in the known universe as these are generated through an algorithm.

This guide will explain the use of UUIDs in the PostgreSQL database.

A Complete Guide to UUIDs in PostgreSQL

UUID or Universal Unique Identifiers data types are used to generate random values which are not sequential and are of 128 bits storage. Randomly generated identities are more secure as they are difficult to perform enumeration attacks on. It is much more difficult to find the order of the table for a random person who does not have any idea about the schema of the PostgreSQL database.

The following are some randomly generated UUID values:

b91cd23b-861c-4cc1-9119-801a4dac1cb9
99fa6aba-1a78-457b-a333-052aa40f5544
a4ae62b8-6615-48b5-a1b0-a05ddba7492c

The UUIDs are the 32-bit identifiers separated into 5 blocks using hyphens and it contains hexadecimal values.

Example 1: Generate UUIDs

To generate random identifiers in Postgres using the UUID, it is required to create its extension first using the following query:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Running the above code will create an extension as it does not come by default with the database:

After creating the extension, the platform has provided the user with these UUID functions which can be used to generate random identifiers:

Use the following UUID function to get the 32-bit UUID:

SELECT uuid_generate_v1();

The above code will display a 32-bit UUID on the screen after its execution:

Use the following function to generate random UUID in PostgreSQL:

SELECT gen_random_uuid();

Example 2: UUID in PostgreSQL Table

After generating UUIDs randomly, use the following command to use UUID in the PostgreSQL table:

CREATE TABLE songs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(50),
  description TEXT,
  created_at TIMESTAMP DEFAULT now()
   )

The above code will create a table named songs and its id field is the primary key of the table. The data type of the id column is UUID and it will generate random values as the id of the songs for each record. The other fields in the above table are the name of the song, its description, and the timestamp of the record creation/insertion:

After the table is created, simply insert data into it using this query:

INSERT INTO songs (name, description)
 VALUES ('Bohemian Rhapsody', 'A classic rock ballad'),
  ('Shape of You', 'A popular pop song'),
  ('Thunderstruck', 'A hit rock song');

The above query will insert values in the songs table and we will only provide/insert the name and description of the table. The table's id will be generated randomly using the UUID data type and the current timestamp will be stored (by default) in the created_at column using the NOW() function:

After that, simply use the following command to display the data stored in the songs table:

SELECT * FROM songs;

Running the above code displays the randomly generated id and inserted data with the timestamp attached:

That’s all about UUID data type and its use in the PostgreSQL database.

Conclusion

Universal Unique Identifiers or UUID data types generate random values to be stored in the PostgreSQL database table. The user needs to create its extension before using it to generate 32-bit identifiers on the screen. Create a table and set the data type of the id as the UUID to generate a random value for each record to keep the database more secure. This guide has explained the UUID data types and their use in the PostgreSQL table.