The uuid-ossp extension provides functions for generating Universally Unique Identifiers (UUIDs) in PostgreSQL. Your Nile database arrives with the uuid-ossp extension already enabled.

​
Overview

The uuid-ossp extension provides several functions for generating UUIDs according to different standards:

  • Version 1: Time-based UUIDs
  • Version 3: Namespace and name-based UUIDs using MD5
  • Version 4: Random UUIDs
  • Version 5: Namespace and name-based UUIDs using SHA-1

Nile includes public.uuid_generate_v7() which generates UUIDs with time-ordered lexicographically sortable strings. It is recommended to use this function for fields that are used in sorting and indexing.

​
UUID Generation Functions

​
UUID Version 4 (Random)

The most commonly used function is uuid_generate_v4(), which generates a random UUID:

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

​
UUID Version 1 (Time-based)

uuid_generate_v1() creates a UUID based on the current timestamp and MAC address:

-- 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();

​
UUID Version 3 (Name-based, MD5)

uuid_generate_v3() creates a UUID based on a namespace and name using MD5:

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

​
UUID Version 5 (Name-based, SHA-1)

uuid_generate_v5() creates a UUID based on a namespace and name using SHA-1:

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

​
Common Use Cases

​
Primary Keys

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

​
Deterministic IDs

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

​
Additional Resources