UUID-OSSP

-- Generate a random UUID
SELECT uuid_generate_v4();
-- Result: 123e4567-e89b-12d3-a456-426614174000 (example)

-- Use in a table
CREATE TABLE documents (
    tenant_id uuid,
    document_id uuid DEFAULT uuid_generate_v4(),
    title text,
    content text,
    PRIMARY KEY (tenant_id, document_id)
);

-- Insert with auto-generated UUID
INSERT INTO documents (tenant_id, title, content) VALUES
    ('11111111-1111-1111-1111-111111111111', 'My Document', 'Content here') returning document_id;

-- Generate a time-based UUID
SELECT uuid_generate_v1();

-- Also available: uuid_generate_v1mc()
-- Similar to v1 but uses a random multicast MAC address
SELECT uuid_generate_v1mc();

-- Generate a UUID from namespace and name using MD5
SELECT uuid_generate_v3(
    'a0eebc99-9c0b-1ef8-b1ff-826046d7f000'::uuid,  -- namespace
    'example.com'                                    -- name
);

-- Common namespace UUIDs
SELECT uuid_generate_v3(uuid_ns_dns(), 'example.com');   -- DNS namespace
SELECT uuid_generate_v3(uuid_ns_url(), 'http://example.com');  -- URL namespace
SELECT uuid_generate_v3(uuid_ns_oid(), '1.2.3.4');      -- OID namespace
SELECT uuid_generate_v3(uuid_ns_x500(), 'CN=Example');  -- X500 namespace

-- Generate a UUID from namespace and name using SHA-1
SELECT uuid_generate_v5(
    'a0eebc99-9c0b-1ef8-b1ff-826046d7f000'::uuid,  -- namespace
    'example.com'                                    -- name
);

-- Common namespace UUIDs
SELECT uuid_generate_v5(uuid_ns_dns(), 'example.com');   -- DNS namespace
SELECT uuid_generate_v5(uuid_ns_url(), 'http://example.com');  -- URL namespace
SELECT uuid_generate_v5(uuid_ns_oid(), '1.2.3.4');      -- OID namespace
SELECT uuid_generate_v5(uuid_ns_x500(), 'CN=Example');  -- X500 namespace

-- Using UUID as primary key
CREATE TABLE contacts (
    tenant_id uuid,
    contact_id uuid DEFAULT uuid_generate_v4(),
    name text,
    email text,
    PRIMARY KEY (tenant_id, contact_id)
);

-- Insert with auto-generated UUID
INSERT INTO contacts (tenant_id, name, email) VALUES
    ('11111111-1111-1111-1111-111111111111', 'john_doe', 'john@example.com') returning contact_id;

-- Generate consistent UUIDs for the same input
CREATE TABLE products (
    tenant_id uuid,
    product_id uuid,
    sku text,
    name text,
    PRIMARY KEY (tenant_id, product_id)
);

-- Insert with deterministic UUID based on SKU
-- If you run this multiple times, you'll get duplicate key error
INSERT INTO products (tenant_id, product_id, sku, name) VALUES
    ('11111111-1111-1111-1111-111111111111',
     uuid_generate_v5(uuid_ns_url(), 'SKU123'),
     'SKU123',
     'Product Name') returning product_id;