Home Dev Lab postgresql

Developer Lab

UUID in PostgreSQL

Production-ready implementation guide with CSPRNG-backed code snippets.

Generate UUID in PostgreSQL

PostgreSQL has a native UUID type and gen_random_uuid() built-in since PG 13 — no extension needed. Stores as 16 bytes, indexes efficiently, and supports UUID as a primary key out of the box.

Quick Reference

Function	Version	PG Version	Notes
gen_random_uuid()	v4	13+	Built-in, no extension needed — recommended
uuid_generate_v4()	v4	9.1+	Requires uuid-ossp extension
uuid_generate_v1()	v1	9.1+	Requires uuid-ossp — timestamp + MAC
uuid_generate_v5()	v5	9.1+	Requires uuid-ossp — deterministic

Primary Implementation

Production Ready

sql snippet — PostgreSQL 13+

-- ── Generate a UUID v4 — no extension needed (PG 13+) ────────
SELECT gen_random_uuid();
-- → a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

-- ── Create a table with UUID primary key ─────────────────────
CREATE TABLE users (
    id         UUID         NOT NULL DEFAULT gen_random_uuid(),
    email      VARCHAR(255) NOT NULL UNIQUE,
    name       VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    PRIMARY KEY (id)
);

-- ── Insert — UUID auto-generated ─────────────────────────────
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice');

-- ── Insert with explicit UUID (idempotent) ────────────────────
INSERT INTO users (id, email, name)
VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'bob@example.com', 'Bob')
ON CONFLICT (id) DO NOTHING;

-- ── Query by UUID ─────────────────────────────────────────────
SELECT id, email, name FROM users
WHERE id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';

-- ── Cast a string to UUID ─────────────────────────────────────
SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid;

All Approaches

uuid-ossp extension — PG 9.1+ (older systems)

sql snippet

-- Enable the extension (one-time, per database)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- UUID v4 — random
SELECT uuid_generate_v4();

-- UUID v5 — deterministic (SHA-1 of namespace + name)
SELECT uuid_generate_v5(uuid_ns_dns(), 'example.com');
-- → always "cfbff0d1-9375-5685-968c-48ce8b15ae17"

-- UUID v1 — timestamp + MAC (avoid for security-sensitive IDs)
SELECT uuid_generate_v1();

pgcrypto extension — alternative for PG 9.x

sql snippet

-- pgcrypto provides gen_random_uuid() for PG 9.4–12
-- (In PG 13+ it's built-in without needing this extension)
CREATE EXTENSION IF NOT EXISTS pgcrypto;

SELECT gen_random_uuid();
-- → a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

Validate UUID input in a function

sql snippet

-- Safe UUID cast — returns NULL instead of throwing on invalid input
CREATE OR REPLACE FUNCTION try_cast_uuid(p_input TEXT)
RETURNS UUID AS $$
BEGIN
    RETURN p_input::UUID;
EXCEPTION WHEN invalid_text_representation THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Usage
SELECT try_cast_uuid('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'); -- → UUID
SELECT try_cast_uuid('not-a-uuid');                            -- → NULL

Real-World Use Cases

1. Multi-tenant schema with UUID PKs

sql snippet

CREATE TABLE tenants (
    id         UUID        NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
    name       TEXT        NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE projects (
    id         UUID        NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
    tenant_id  UUID        NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name       TEXT        NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- UUID PKs make cross-tenant merges and data migrations trivial
-- No risk of ID collisions when combining data from multiple tenants

2. Idempotent upsert with UUID

sql snippet

-- Generate UUID in application, pass it in for idempotent inserts
-- Safe to retry — duplicate UUIDs are ignored
INSERT INTO orders (id, customer_id, total, status)
VALUES (
    'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
    'b1ffcd00-0d1c-5f09-cc7e-7cc0ce491b22',
    99.99,
    'pending'
)
ON CONFLICT (id) DO NOTHING
RETURNING id, status;

3. Deterministic UUID v5 for deduplication

sql snippet

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Same event always gets the same UUID — safe to process multiple times
CREATE OR REPLACE FUNCTION event_id(source TEXT, event_type TEXT, ts TEXT)
RETURNS UUID AS $$
    SELECT uuid_generate_v5(
        uuid_ns_oid(),
        source || ':' || event_type || ':' || ts
    );
$$ LANGUAGE SQL IMMUTABLE;

-- Usage
SELECT event_id('payments', 'charge.created', '2026-05-01T12:00:00Z');
-- → always the same UUID for the same inputs

Common Mistakes

✗

Storing UUIDs as TEXT or VARCHAR

PostgreSQL's native UUID type stores 16 bytes. TEXT stores 36+ bytes and loses type safety — you can't use ::uuid casting, UUID-specific operators, or index optimizations. Always use the UUID column type.

✗

Using uuid_generate_v4() when gen_random_uuid() is available

gen_random_uuid() is built into PostgreSQL 13+ — no extension needed. uuid_generate_v4() requires the uuid-ossp extension. Prefer the built-in function for new projects.

✗

Not using ON CONFLICT DO NOTHING for idempotent inserts

When generating UUIDs in the application and passing them to PostgreSQL, use INSERT ... ON CONFLICT (id) DO NOTHING to make inserts idempotent — safe to retry on network failures without duplicate records.

How It Works

gen_random_uuid() uses PostgreSQL's internal CSPRNG (seeded from /dev/urandom) to generate 16 random bytes, then sets the version (4) and variant bits per RFC 4122.

The native UUID type stores exactly 16 bytes on disk. PostgreSQL can index UUID columns efficiently — the B-tree index on a UUID column is the same size as on a BIGINT column.

UUID values are case-insensitive in PostgreSQL — 'A0EEBC99-...' and 'a0eebc99-...' are equal.

Output Formats

gen_random_uuid()

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

Storage size

16 bytes (UUID type)

Cast to text

gen_random_uuid()::text

Best Practices

✓

Use gen_random_uuid() on PG 13+ — no extension needed.

✓

Always use the native UUID column type — never TEXT or VARCHAR.

✓

Generate UUIDs in the application layer for idempotent inserts with ON CONFLICT DO NOTHING.

Performance

PostgreSQL's native UUID type is stored as 16 bytes — same as two BIGINT values. Index lookups are fast and the index size is compact.

Random UUID v4 PKs cause some B-tree fragmentation on very large tables. For write-heavy tables with 100M+ rows, consider using ULIDs or UUID v7 (time-ordered) generated in the application layer.

Version Requirements

gen_random_uuid()PG 13+ (built-in)

gen_random_uuid() via pgcryptoPG 9.4–12

uuid_generate_v4()PG 9.1+ (uuid-ossp)

Native UUID typePG 8.3+

Security

Entropy source: PostgreSQL's internal CSPRNG seeded from /dev/urandom. gen_random_uuid() is cryptographically secure.

Suitable for primary keys, session tokens stored in the DB, and any identifier that must be globally unique. Avoid uuid_generate_v1() for security-sensitive IDs — it leaks the server's MAC address.