Generate UUID in PostgreSQL

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

-- 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

-- 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

-- 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

1. Multi-tenant schema with UUID PKs

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

-- 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

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

How do I generate a UUID in PostgreSQL?

PostgreSQL has a native uuid type and a built-in gen_random_uuid() function (since PostgreSQL 13) that returns a random v4 UUID with no extension needed. It stores as 16 bytes and indexes efficiently. On older versions, enable the uuid-ossp or pgcrypto extension.

Is gen_random_uuid() cryptographically secure?

Yes. gen_random_uuid() uses the platform CSPRNG (operating system secure random source), suitable for session tokens, API keys, and idempotency keys. Do not use non-cryptographic random sources for security-sensitive identifiers.

What is the difference between UUID v4 and v7 in PostgreSQL?

UUID v4 (gen_random_uuid() or equivalent) is fully random and not sortable. UUID v7 embeds a millisecond timestamp for chronological sorting (RFC 9562). Use v4 for general-purpose IDs; use v7 for database primary keys at scale.

Do I need to install a package for UUID generation in PostgreSQL?

No additional package is required for basic v4 generation in PostgreSQL. Check the Installation section for version-specific notes.

How do I validate a UUID string in PostgreSQL?

Use the platform's UUID parse/validation function, or test against the RFC 4122 regex: /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i. Always validate external input at API boundaries.

Should I use UUIDs as database primary keys in PostgreSQL applications?

UUIDs work well as primary keys for distributed systems. Prefer native UUID/BINARY(16) column types over VARCHAR(36). For very large tables, consider UUID v7 for better B-tree insert locality.

Can I generate UUIDs in PostgreSQL without a network connection?

Yes. UUID generation uses local OS entropy sources and does not require network access. Each call is independent and thread-safe on modern platforms.

What output formats are available in PostgreSQL?

The standard hyphenated lowercase string (36 chars) is the default. Most APIs also support 32-char hex (no hyphens) and 16-byte binary formats. Use string format for APIs and binary for database storage.

What RFC standards apply to PostgreSQL UUID generation?

Version 4 UUIDs follow RFC 4122. UUID v7 follows RFC 9562 (May 2024). Ensure your chosen method produces compliant version and variant bits.

When should I avoid UUID v1?

Avoid UUID v1 in security-sensitive contexts - it embeds MAC address and timestamp information. Prefer v4 (gen_random_uuid() or equivalent) unless you need legacy Cassandra timeuuid compatibility.