PostgreSQL – UUID Data Type
UUID is an abbreviation for Universal Unique Identifier defined by RFC 4122 and has a size of 128-bit. It is created using internal algorithms that always generate a unique value.
PostgreSQL has its own UUID data type and provides modules to generate them. UUID is generally used in distributed systems as it guarantees a singularity better than the SERIAL data type which produces only singular values within a sole database.
PostgreSQL enables you to store and compare UUID values but it does not incorporate functions for producing the UUID values in its core. Instead, it depends on the third-party modules that offer certain algorithms to generate UUIDs. For example the uuid-ossp
module offers some handy functions that carry out standard algorithms for generating UUIDs.
To install the “uuid-ossp” extension use the below command:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
For generating a UUID values based on the blend of computer’s MAC address, present timestamp, and a random value, the uuid_generate_v1()
function can be used as shown below:
SELECT uuid_generate_v1();
It would result in output similar to the image below:
For generating a UUID value solely based on random numbers, the uuid_generate_v4()
function can be used as shown below:
SELECT uuid_generate_v4();
It would result in output similar to the image below:
Example:
In this example we will make a table whose primary key is a UUID data type. In supplement, the values of the primary key column will be produced automatically through the uuid_generate_v4()
function.
First, create a contacts table using the following statement:
CREATE TABLE contacts ( contact_id uuid DEFAULT uuid_generate_v4 (), first_name VARCHAR NOT NULL, last_name VARCHAR NOT NULL, email VARCHAR NOT NULL, phone VARCHAR, PRIMARY KEY (contact_id) );
Now we insert some data to our contacts table as below:
INSERT INTO contacts ( first_name, last_name, email, phone ) VALUES ( 'Raju', 'Kumar', 'rajukumar@gmail.com', '408-237-2345' ), ( 'Nikhil', 'Aggarwal', 'nikhilaggarwal@gmail.com', '408-237-2344' ), ( 'Anshul', 'Aggarwal', 'anagg@hotmail.com', '408-237-2343' );
Now we query all rows in the contacts table using the below command:
SELECT * FROM contacts;
Output:
Contact Us