Generate a random UUID primary key during insert in PostgreSQL

It is very common to use a UUID as primary key for a SQL table. You can generate a unique random UUID on the client side and then insert it into the database. However, there is a simpler way: you can ask the database to automatically generate it on every insert. With PostgreSQL, it is very simple. Imagine you want to create a table called SPACE with a UUID primary key ID that is randomly generated by the database. You simply need to create your SQL table as follows:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE SPACE (ID UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
                    TYPE VARCHAR(50) NOT NULL,
                    DATE TIMESTAMP WITH TIME ZONE NOT NULL);

The random generation of the primary key ID is handled by this code:

DEFAULT uuid_generate_v4()

Then, when you insert a row in this table, you don’t need to specify the value for the primary key ID, but only for the other columns (in this case TYPE and DATE). So your insert code will look like this:

INSERT INTO SPACE (TYPE,DATE) VALUES ('20','2021-08-23 17:40:15.977594');

That’s it for this tutorial ! Please leave a comment below if you have any question, we reply within 24 hours.