How to Generate a UUID for the Insert Statement in PostgreSQL
UUID stands for Universally Unique Identifier, which is defined by the RFC-4122. We can use this while creating the table.
Use UUID in the CREATE TABLE
UUID offers 32-digit hexadecimal code, basically 128-bit. It generates a unique string every time.
We can generate UUID from Postgres. But it will show an error the first time like the following:
postgres=# select uuid_generate_v4();
ERROR: function uuid_generate_v4() does not exist
LINE 1: select uuid_generate_v4()
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
So, we need to install the uuid-ossp module. Here’s how you’ll do it:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
Now, you can create all versions of the UUID. Here are the examples:
postgres=# SELECT uuid_generate_v1();
uuid_generate_v1
--------------------------------------
22e2579a-1c76-11ed-979f-eba332a1af20
(1 row)
postgres=# SELECT uuid_generate_v4();
uuid_generate_v4
--------------------------------------
db1c0f9f-dedb-459e-b267-d56ff06e6714
(1 row)
Let’s create a table with the UUID as one of the table’s columns.
CREATE TABLE contacts (
id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
full_name VARCHAR NOT NULL,
email VARCHAR NOT NULL,
phone VARCHAR
);
In this table, if we insert a row, a UUID will be generated and assigned as the id by default.
INSERT INTO contacts(full_name, email, phone)
VALUES('Alex','alex@gmail.com','01912334355');
INSERT INTO contacts(full_name, email, phone)
VALUES('John','john@gmail.com','123456789');
-- Let's see the output
postgres=# select * from contacts;
id | full_name | email | phone
--------------------------------------+-----------+----------------+-------------
5685a3d5-7de0-47ce-9fe4-19bbbc81b531 | Alex | alex@gmail.com | 01912334355
4e4555db-9ea9-4b0b-9a71-d52d176d063d | John | john@gmail.com | 123456789
(2 rows)
Here, you can see that the id is generated as the uuid.
Insert the UUID Within the INSERT Statement
Let’s modify the above table with more two-column that will hold the UUID_v1 and UUID_v4. Here’s the modified table:
CREATE TABLE contacts
(
id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
full_name VARCHAR NOT NULL,
id_v1 uuid,
id_v4 uuid,
email VARCHAR NOT NULL,
phone VARCHAR
);
INSERT INTO contacts
(full_name, id_v1, id_v4, email, phone)
VALUES('Alex', uuid_generate_v1(),
uuid_generate_v4(),
'alex@gmail.com', '01912334355');
INSERT INTO contacts
(full_name, id_v1, id_v4, email, phone)
VALUES('John', uuid_generate_v1(),
uuid_generate_v4(),
'john@gmail.com', '123456789');
Here’s the Output:
postgres=# select full_name, id_v1, id_v4 from contacts;
full_name | id_v1 | id_v4
-----------+--------------------------------------+--------------------------------------
Alex | 23438c20-1c78-11ed-ad33-0bc676d6e82c | b751fa4f-39da-4e3d-ba36-b79edd1ccf9e
John | 24545572-1c78-11ed-9d3c-b3409499f6de | dece10ab-56ab-4bad-b27b-5e9c15060c52
(2 rows)
Initially, the UUID generator is not present in the PostgreSQL. To avoid the error, we need to perform create extension in Postgres so that the UUID generator will be available to the Postgres statement.
To know more about the Postgres UUID, visit the following blog.
