PostgreSQL UUID Data Type

UUID stands for Universal Unique Identifier which is defined by RFC 4122. The UUID value is calculated by an algorithm and the value is 128-bit long. The UUID generates a unique value that is better than serial data that generates singular values. PostgreSQL does not have any function to create the UUID value. It depends on the third-party module named uuid-ossp that accesses the necessary functions to create the UUID value. The way to install the uuid-ossp module and the uses of the UUID data type in the PostgreSQL database have been shown in this tutorial.

Pre-requisites:

You have to install the latest version of PostgreSQL packages on the Linux operating system before executing the SQL statements shown in this tutorial. Run the following commands to install and start the PostgreSQL:

$ sudo apt-get -y install postgresql postgresql-contrib

$ sudo systemctl start postgresql.service

Run the following command to login to PostgreSQL with root permission:

$ sudo -u postgres psql

Install uuid-ossp module:

If the uuid-ossp module is not installed before in the PostgreSQL, then run the following command to install the module. Here, the ‘IF NOT EXISTS’ clause has been used to prevent the re-installation of the module if it is installed already.

# CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

After installing the uuid-ossp module successfully, run the following SELECT command to generate a UUID based on the MAC address of the computer, timestamp value, and a random value. Here, the uuid_generate_v1() function has been used to generate the unique value.

# SELECT uuid_generate_v1();

The following output will appear after executing the above query:

Run the following SELECT command to generate UUID based on the random value. Here, the uuid_generate_v4() function has been used to generate the unique value.

# SELECT uuid_generate_v4();

The following output will appear after executing the above query:

Use of UUID in PostgreSQL:

Before creating any table with the Boolean data type you have to create a PostgreSQL database. So, run the following command to create a database named ‘testdb’:

# CREATE DATABASE testdb;

The following output will appear after creating the database:

Create a table using the UUID data type:

The invoice ID is always unique for any invoice document. The UUID data type is a better option to create a unique ID. Create a table named ‘Invoice’ in the current database with four fields. The first field name is Invoice_id which is the primary key of the table. The data type of this field is UUID. If no value is provided for the Invoice_id field at the time of inserting data, then the default value will be generated by using the uuid_generate_v4() function. The second field name is Order_date and the data type of this field is DATE which will be used to store the order date. The third field name is Customer_id and the data type of this field is CHAR (6) to store the string value of 4 characters. The fourth field is Order_amount and the data type of this field is INT to store order amount.

# CREATE TABLE Invoice (

Invoice_id uuid DEFAULT uuid_generate_v4 (),
Order_date DATE NOT NULL,
Customer_id CHAR(6) NOT NULL,
Order_amount INT NOT NULL,
PRIMARY KEY (Invoice_id));

The following output will appear if the table is created successfully:

Run the following INSERT query to insert four records into the Invoice table. The values for the Invoice_id field are not given in the following query. So, the uuid_generate_v4() function will generate the values for the Invoice_id field.

# INSERT INTO Invoice (Order_date, Customer_id, Order_amount)

VALUES('May 10, 2022', '893456', 70000),
('May 12, 2022', '885634', 65000),
('May 17, 2022', '985632', 30000),
('May 21, 2022', '891256', 23000);

The following output will appear after executing the above query. The output shows that four records have been inserted properly into the Invoice table:

Run the following SELECT query to read all records from the Invoice table:

# SELECT * FROM Invoice;

The following output will appear after executing the above query. The output shows the four unique ID generated for the Invoice_id field:

Run the following SELECT query that will read those records from the Invoice table where the value of the order_date field is less than ‘2022-05-15’:

# SELECT * FROM Invoice WHERE order_date < '2022-05-15';

The following output will appear after executing the above query. The table contains two records where the value of the order_date is less than ‘2022-05-15’.

Run the following SELECT query that will read those records from the Invoice table where the value of the order_date field is between ‘2022-05-15’ and ‘2022-05-20’.

# SELECT * FROM Invoice WHERE order_date BETWEEN '2022-05-15' AND '2022-05-20';

The following output will appear after executing the above query. The table contains one record that matches the condition.

Run the following SELECT query that will read those records from the Invoice table where the value of the order_amount is greater than 50000.

# SELECT * FROM Invoice WHERE order_amount > 50000;

The following output will appear after executing the above query. Two records exist in the table where the value of the order_amount is greater than 50000.

Run the following SELECT query that will read those records from the Invoice table where the value of the order_amount is greater than 50000 and less than 70000.

# SELECT * FROM Invoice WHERE order_amount > 50000 AND order_amount < 70000;

The following output will appear after executing the above query. The table contains one record that matches the condition.

Conclusion:

The way of using the UUID data type and the use of this data type to generate unique ID in PostgreSQL tables have been shown in this tutorial. The purpose of using the UUID data type will be cleared after reading this tutorial properly.