EDUCBA

Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL UUID

PostgreSQL UUID

PostgreSQL UUID

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. It is defined by RFC 4122 standards in PostgreSQL; the Value of UUID is 128 bit which was generated by an algorithm that makes the unique identifier number within the database. UUID is mostly used in distributed application databases to generate a unique number within the database because UUID is more unique as compare to 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 is as follows.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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, which was used in the table on which we have created a UUID data type in PostgreSQL. We can create a UUID data type by using the uuid_generate_v1 and uuid_generate_v4 function on the column.
  2. Table name – Table name used to create a new table by using a UUID data type on the column. We can create a UUID data type on the column.
  3. Create – Create a keyword that is used to create a new table by using a UUID data type on the column.
  4. Data type – We can define different data types to 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 on 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 function by using a select statement.
  7. uuid_generate_v1 – This defines as a generate UUID number by using a combination of the mac address of the computer, 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 defines as a generate UUID number by 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 are as follows.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

PostgreSQL UUID output 1

  • The above command creates a UUID-OSSP extension if not exist in the database. By default, this extension is not available; we need to create this after installation to use a UUID data type.
  • It is also known as a universally unique identifier; it is used to generate a unique value within a database.
  • The UUID data type is unique as compared to the serial data type.
  • PostgreSQL UUID as defined by RFC 4122 standards in PostgreSQL, Value of UUID is a 128 bit which was generated by algorithm this makes the unique identifier number within the database.
  • UUID is mostly used in distributed application databases to generate a unique number within the database because UUID is more unique as compare to serial data type in PostgreSQL.
  • It is the 32-bit sequence number of hexadecimal digits separated by a hyphen.
  • It is more unique as compare to a serial in PostgreSQL because it is a 32-bit combination of hexadecimal numbers.
  • There are two functions of PostgreSQL UUID data types.
  1. uuid_generate_v1 ()
  2. uuid_generate_v4 ()
  • Uuid_generate_v1 define as generating a UUID number by using a combination of the mac address of the computer, 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 of PostgreSQL UUID

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

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

select uuid_generate_v1 ();

PostgreSQL UUID output 2

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

select uuid_generate_v4 ();

output 3

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

In the below example, we have to create a table and inserting value by 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);

output 4

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;

output 5

Advantages of using UUID in PostgreSQL

  • Below are the advantages of the PostgreSQL UUID data type are as follows.
  • PostgreSQL UUID data type used to generate a sequential number.
  • The UUID data type is more unique as compared to 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

It is also known as a universally unique identifier; it is used to generate 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

This is a guide to PostgreSQL UUID. Here we discuss How does UUID data type work in PostgreSQL, along with the examples and advantages. You may also have a look at the following articles to learn more –

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