🪪

PostgreSQL UUID Generation Functions

Complete reference for PostgreSQL UUID functions including the built-in gen_random_uuid() for UUID v4 and the uuid-ossp extension functions. Covers primary key usage patterns, UUID v7 time-sortable identifiers, and performance trade-offs vs serial integers. Updated for PostgreSQL 16.

4 functions

Jump to function

gen_random_uuid uuid_generate_v4 uuid_generate_v7 uuid_nil

What are PostgreSQL UUID Generation Functions?

PostgreSQL UUID functions generate universally unique identifiers suitable for distributed primary keys and globally unique record identifiers. gen_random_uuid() generates a random UUID v4 and is available without any extension from PostgreSQL 13 onwards. UUID v7 (time-sortable, B-tree friendly) is available from PostgreSQL 17 or via the pg_uuidv7 extension. UUIDs are preferred over serial integers when records are created concurrently across multiple application servers or database instances.

gen_random_uuid

PG 13+→ uuid

Generates a version 4 (random) UUID using the pgcrypto random number generator.

DeveloperDBASecurity

Signature

gen_random_uuid ( ) → uuid

Examples

sql

SELECT gen_random_uuid();

→a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 (varies)

sql

INSERT INTO items (id, name) VALUES (gen_random_uuid(), 'Widget');

→Row with random UUID primary key

sql

SELECT gen_random_uuid() FROM generate_series(1,5);

→5 unique UUIDs

sql

UPDATE sessions SET token = gen_random_uuid() WHERE expired = true;

→Expired sessions get fresh random tokens

⚠Anti-Pattern— Installing pgcrypto or uuid-ossp just for UUID generation on PG 13+

Before PostgreSQL 13, `uuid_generate_v4()` from the uuid-ossp extension was the standard way to generate random UUIDs. Since PG 13, `gen_random_uuid()` is built-in and requires no extension — installing uuid-ossp adds unnecessary dependency.

✓ Instead: Use `gen_random_uuid()` directly on PostgreSQL 13+. If compatibility with older versions is needed, check with `SELECT current_setting('server_version_num')::int >= 130000` first.

Set `DEFAULT gen_random_uuid()` on UUID columns so every INSERT automatically gets a unique identifier without application code: `id uuid DEFAULT gen_random_uuid() PRIMARY KEY`.

example

CREATE TABLE orders (id uuid DEFAULT gen_random_uuid() PRIMARY KEY, amount numeric);

→Each row auto-assigned a random UUID

uuid_generate_v4

PG 8.3+→ uuid

Generates a version 4 random UUID using the uuid-ossp extension.

DeveloperDBASecurity

Signature

uuid_generate_v4 ( ) → uuid

Examples

sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; SELECT uuid_generate_v4();

→a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 (varies)

sql

INSERT INTO sessions (id, user_id) VALUES (uuid_generate_v4(), 42);

→New session row with random UUID

sql

SELECT uuid_generate_v4() AS request_id FROM generate_series(1,3);

→3 distinct random v4 UUIDs

sql

ALTER TABLE documents ALTER COLUMN id SET DEFAULT uuid_generate_v4();

→Column default updated to generate random UUIDs on insert

⚠Anti-Pattern— Installing uuid-ossp solely for uuid_generate_v4() on PostgreSQL 13+

The uuid-ossp extension was the standard source of v4 UUIDs before PG 13. On modern PostgreSQL, adding this extension dependency is unnecessary since `gen_random_uuid()` provides the same functionality with no extension required.

✓ Instead: Use built-in `gen_random_uuid()` on PG 13+. Only install uuid-ossp if you need uuid_generate_v1(), uuid_generate_v3(), or uuid_generate_v5() which have no built-in equivalents.

`gen_random_uuid()` is built-in since PostgreSQL 13 (no extension needed) and uses cryptographically secure randomness. Use it instead of `uuid_generate_v4()` which requires the `uuid-ossp` extension.

example

SELECT gen_random_uuid();  -- No extension required

→Random v4 UUID without any extension

uuid_generate_v7

v7 UUIDs require pg_uuidv7 extension or custom implementation→ uuid

Generates a version 7 UUID — a time-ordered UUID with monotonically increasing values based on Unix timestamp milliseconds. Requires custom function or pg_uuidv7 extension.

DeveloperDBASecurity

Signature

uuidv7 ( ) → uuid

Examples

sql

SELECT uuidv7();

→01905c2f-3a8b-7000-9a6e-3b8c4d5e6f70 (varies, but sortable)

sql

SELECT uuidv7() AS id FROM generate_series(1,3);

→3 time-ordered UUIDs

sql

CREATE TABLE events (id uuid DEFAULT uuidv7() PRIMARY KEY, payload jsonb);

→Table where rows insert in time order, keeping B-tree index compact

sql

SELECT uuidv7() AS id, now() AS created_at;

→Monotonically increasing UUID paired with its creation timestamp

⚠Anti-Pattern— Using random v4 UUIDs as primary keys on high-insert-rate tables

Random v4 UUIDs scatter inserts across random B-tree index pages, causing constant page splits and cache misses. On a table receiving thousands of inserts per second, UUID v4 primary keys cause significant write amplification and index bloat.

✓ Instead: Use UUIDv7 (`uuidv7()` via pg_uuidv7 extension) for primary keys on insert-heavy tables. UUIDv7 UUIDs are time-ordered, so inserts append to the end of the index like sequential integers.

Random v4 UUIDs cause random B-tree insertions (page splits, fragmentation). UUIDv7 UUIDs are time-ordered, so inserts go to the end of the index like sequential integers — much better for insert-heavy tables.

example

CREATE TABLE events (id uuid DEFAULT uuidv7() PRIMARY KEY, ts timestamptz DEFAULT now());

→Time-ordered UUIDs with excellent index performance

uuid_nil

PG 8.3+→ uuid

Returns the nil (all-zeros) UUID: 00000000-0000-0000-0000-000000000000.

DeveloperDBASecurity

Signature

uuid_nil ( ) → uuid

Examples

sql

SELECT uuid_nil();

→00000000-0000-0000-0000-000000000000

sql

SELECT * FROM entities WHERE parent_id = uuid_nil();

→Root entities using nil UUID as sentinel

sql

INSERT INTO nodes (id, parent_id) VALUES (gen_random_uuid(), uuid_nil());

→Root node inserted with nil UUID as explicit 'no parent' marker

sql

SELECT count(*) FROM jobs WHERE owner_id != uuid_nil();

→Count of jobs that have a real owner assigned

⚠Anti-Pattern— Mixing NULL and nil UUID as dual 'no value' sentinels in the same schema

Using both `NULL` and `uuid_nil()` to represent 'no value' in the same column creates ambiguous query logic. Queries checking `WHERE parent_id IS NULL` miss nil-UUID rows, while `WHERE parent_id = uuid_nil()` misses actual NULLs.

✓ Instead: Choose one convention per column: either allow `NULL` (and use `IS NULL`) or use `uuid_nil()` as a sentinel with a `NOT NULL` constraint. Never mix both in the same column.

When a NOT NULL UUID column needs a 'no value' sentinel, use `uuid_nil()` instead of NULL. This allows NOT NULL constraints while still indicating absence. Compare with `= '00000000-0000-0000-0000-000000000000'::uuid`.

example

SELECT * FROM tasks WHERE parent_uuid != uuid_nil();

→Tasks that have a real parent (not the nil sentinel)

PostgreSQL UUID Generation Functions

Jump to function

What are PostgreSQL UUID Generation Functions?

gen_random_uuid

Signature

Examples

uuid_generate_v4

Signature

Examples

uuid_generate_v7

Signature

Examples

uuid_nil

Signature

Examples

Related PostgreSQL Categories