uuid-ossp

Generate universally unique identifiers (UUIDs) in SQL.

Built-in (No Extension Required)

uuid_generate_v4() is available by default without any extension:

SQL

-- Generate a random UUID (v4)
SELECT uuid_generate_v4();
-- Result: 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'

-- Use in table definition
CREATE TABLE users (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  name TEXT
);

-- Insert without specifying id
INSERT INTO users (name) VALUES ('Alice');

Extension (PostgreSQL Compatibility)

For full PostgreSQL compatibility, you can also install the extension:

SQL

CREATE EXTENSION "uuid-ossp";

Available Functions

Function	Description
uuid_generate_v4()	Random UUID v4 (most common)
gen_random_uuid()	Alias for uuid_generate_v4()
uuidv7()	Time-ordered UUID v7 (recommended for primary keys)

UUID v7 (Time-Ordered)

UUID v7 embeds a timestamp, making it ideal for primary keys where natural ordering by creation time is desired:

SQL

-- Generate UUID v7
SELECT uuidv7();
-- Result: '018e1c6a-5b00-7abc-8def-1234567890ab'

-- UUIDs are naturally sorted by creation time
CREATE TABLE events (
  id UUID DEFAULT uuidv7() PRIMARY KEY,
  name TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Insert multiple rows
INSERT INTO events (name) VALUES ('first'), ('second'), ('third');

-- UUIDs are already in chronological order
SELECT id, name FROM events ORDER BY id;

Why UUID v7 over v4?

• Time-ordered: Better for B-tree index performance
• Sortable: Natural chronological ordering without timestamp column
• Unique: Still globally unique like v4

Common Patterns

SQL

-- Generate multiple UUIDs
SELECT uuid_generate_v4() FROM generate_series(1, 5);

-- UUID as text
SELECT uuid_generate_v4()::text;

-- Check if valid UUID
SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid;