PostgreSQL UUID

Updated May 24, 2023

Introduction to PostgreSQL UUID

PostgreSQL UUID is also known as a universally unique identifier; it is used to generate a unique value within a database. RFC 4122 standards in PostgreSQL define it; the Value of UUID is 128 bit, generated by an algorithm that makes the unique identifier number within the database. UUID is mainly used in distributed application databases to generate a unique number within the database because UUID is more unique than a serial data type. The UUID data type is unique as compared to the serial data type.

Syntax

Below is the syntax of the data type as follows.

1. Syntax of creating a table using data type.

CREATE TABLE table_name(
Column_name uuid DEFAULT uuid_generate_v4(),
Column_namedata type NOT NULL,
Column_nameData type,
PRIMARY KEY (column_name));

2. SELECT uuid_generate_v1() (Generating UUID using a combination of MAC address of Computer, timestamp, and random value);

3. SELECT uuid_generate_v4 () (Generating UUID by using only random numbers.);

Below is the parameter description of the above syntax.

1. Column 1 to column N – Column 1 to column N, used in the table on which we created a UUID data type in PostgreSQL. We can create a UUID data type on the column using the uuid_generate_v1 and uuid_generate_v4 functions.
2. Table name – The table name creates a new table using a UUID data type on the column. We can create a UUID data type on the column.
3. Create – Create a keyword used to create a new table using a UUID data type on the column.
4. Data type – We can define different data types for the different columns while creating a table.
5. Primary key – We have created a primary key on the UUID column; while creating a new table, we can also create a primary key later in the column.
6. Select – Select is used to select the UUID data type in PostgreSQL. We can select a value of uuid_generate_v1 and uuid_generate_v4 data type functions by using a select statement.
7. uuid_generate_v1 – This is defined as a generated UUID number using a combination of the computer’s Mac address, the current timestamp, and any random number. We can generate a UUID number by using this function. To use this function, we need to create an extension of UUID-OSSP in PostgreSQL.
8. uuid_generate_v4 – This is defined as generating UUID numbers using only random numbers. We can generate a UUID number by using this function. To use this function, we need to create an extension of UUID-OSSP in PostgreSQL.

How does UUID data type work in PostgreSQL?

Below is the working of the data type.

• To use the UUID function, we need to create an extension of UUID-OSSP in PostgreSQL.
• The below example states that to create an extension of UUID-OSSP is as follows.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

• The above command creates a UUID-OSSP extension if it does not exist in the database. By default, this extension is unavailable; we need to create this after installation to use a UUID data type.
• A universally unique identifier serves as an identifier used for generating a unique value within a database.
• The UUID data type is unique as compared to the serial data type.
• PostgreSQL follows the RFC 4122 standards for UUIDs. PostgreSQL generates a UUID as a 128-bit value using an algorithm that guarantees uniqueness within the database, thus making it a unique identifier.
• Distributed application databases often utilize UUIDs to generate unique numbers within the database. The preference for UUIDs over serial data types in PostgreSQL stems from their higher level of uniqueness.
• The UUID consists of a sequence of 32-bit hexadecimal digits separated by hyphens.
• It is more unique than a serial in PostgreSQL because it is a 32-bit combination of hexadecimal numbers.
• There are two functions of UUID data types.

1. uuid_generate_v1 ()
2. uuid_generate_v4 ()

• Uuid_generate_v1 generates a UUID number by combining the computer’s Mac address, current timestamp, and any random number. We can generate a UUID number by using this function in PostgreSQL. To use this function, we need to create an extension of UUID-OSSP in PostgreSQL.
• Uuid_generate_v4 defines as a generate UUID number by using only a random number. We can generate a UUID number by using this function. To use this function, we need to create an extension of UUID-OSSP in PostgreSQL.

Examples

Below is an example of the UUID data type in PostgreSQL is as follows.

1. uuid_generate_v1 () – We can see the value of uuid_generate_v1 () as below. Below is the value of the uuid_generate_v1 () function.

select uuid_generate_v1 ();

2. uuid_generate_v4 () –We can see the value of uuid_generate_v4 () as below. Below is the value of the uuid_generate_v4 () function.

select uuid_generate_v4 ();

3. Define UUID data type at the time of table creation

In the example below, we have to create a table and insert value using the UUID data type.

Create table –

CREATE TABLE Employee_UUID (emp_id uuid DEFAULT uuid_generate_v1(),emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14),emp_salary INT NOT NULL, date_of_joining date NOT NULL);

Insert Value –

INSERT INTO Employee_UUID (emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES ('ABC', 'Pune', '1234567890', 20000, '01-01-2020');
INSERT INTO Employee_UUID (emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES ('PQR', 'Pune', '1234567890', 20000, '01-01-2020');
INSERT INTO Employee_UUID (emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES ('XYZ', 'Mumbai', '1234567890', 35000, '02-01-2020');
select * from Employee_UUID;

Advantages of using UUID in PostgreSQL

• Below are the advantages of the PostgreSQL UUID data type are as follows.
• PostgreSQL UUID data type is used to generate a sequential number.
• The UUID data type is more unique than the serial data type.
• The UUID data type will generate a 32-bit sequential value, so it is more unique.
• Performance is faster than other serial data types.
• We can use an alternate function to create a UUID.

Conclusion

A universally unique identifier (UUID) serves as an identifier used for generating a unique value within a database. The UUID data type is unique as compared to a serial data type.PostgreSQL UUID, as defined by RFC 4122 standards, the Value of UUID is 128 bit.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL UUID” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. Wildcards in PostgreSQL
2. Sequence in PostgreSQL
3. SQL Full Join
4. MySQL Self Join