EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL UUID

PostgreSQL UUID

Updated May 24, 2023

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

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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

PostgreSQL UUID output 1

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

PostgreSQL UUID output 2

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

output 3

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

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 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
PROGRAMMING LANGUAGES Course Bundle - 54 Courses in 1 | 4 Mock Tests
338+ Hours of HD Videos
54 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SELENIUM Course Bundle - 15 Courses in 1 | 9 Mock Tests
39+ Hours of HD Videos
15 Courses
9 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
IOT System Course Bundle - 7 Courses in 1
43+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
JENKINS Course Bundle - 6 Courses in 1
15+ Hour of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
POSTGRESQL Course Bundle - 5 Courses in 1 | 1 Mock Test
 15+ Hour of HD Videos
5 Courses
1 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more