> ## Documentation Index
> Fetch the complete documentation index at: https://thenile.dev/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# UUID-OSSP

> UUID generation functions for PostgreSQL

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

<Tip>
  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.
</Tip>

## UUID Generation Functions

### UUID Version 4 (Random)

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

```sql theme={null}
-- 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:

```sql theme={null}
-- 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:

```sql theme={null}
-- 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:

```sql theme={null}
-- 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

```sql theme={null}
-- 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

```sql theme={null}
-- 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

* [PostgreSQL UUID Documentation](https://www.postgresql.org/docs/current/uuid-ossp.html)
* [UUID RFC 9562](https://www.rfc-editor.org/rfc/rfc9562.html)