Generate a UUID as PostgreSQL default value

Shawn OY
Shawn OY
Feb 20 · 2 min read
Photo by Kevin Ku on Unsplash

As we know PostgreSQL has UUID data type but you can not generate a UUID as the default value by default, in general, we generate a UUID first and insert the UUID into the database, might it is very bothered.

What if we can automatically generate a UUID as the default value? sounds cool and we have two ways to do it.

#0x01 pgcrypto extension

This extension provides a function to generate a version 4 UUID, we must enable the extension first.

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

Now we can try to generate a UUID.

SELECT gen_random_uuid();

We will get a UUID and we can use gen_random_uuid() as the default value.

id UUID NOT NULL DEFAULT gen_random_uuid()

Full example:

CREATE EXTENSION IF NOT EXISTS "pgcrypto";CREATE TABLE IF NOT EXISTS members (
id UUID PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
name VARCHAR(20) NOT NULL
);

#0x02 uuid-ossp extension

This extension provides many functions to generate a UUID, support version 1, 3, 4 and 5, pgcrypto only generates a version 4 UUID, if you need use others version, you should use uuid-ossp.

Now, let’s enable the extension first.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Try to generate a UUID.

SELECT uuid_generate_v4();

Or you can generate a version 1 UUID.

SELECT uuid_generate_v1();

Full example:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";CREATE TABLE IF NOT EXISTS member1 (
id UUID PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),
name VARCHAR(20) NOT NULL
);

How to manage the extension?

List all extensions:

SELECT * FROM pg_extension;

Delete a extension:

DROP EXTENSION IF EXISTS "pgcrypto";

More From Medium

Also tagged Postgresql

Also tagged Tutorial

Top on Medium

Mar 25 · 22 min read

28K

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade