Generate UUID in PostgreSQL

PostgreSQL has excellent built-in UUID support through extensions. The most common approach is using the uuid-ossp extension or the newer gen_random_uuid() function:

-- Enable UUID extension (choose one)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- OR for PostgreSQL 13+
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Generate UUID version 4 (random) - Modern approach
SELECT gen_random_uuid();

-- Generate UUID version 4 using uuid-ossp
SELECT uuid_generate_v4();

For generating specific UUID versions, PostgreSQL's uuid-ossp extension provides comprehensive support:

-- Enable the uuid-ossp extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Generate UUID version 1 - time based
SELECT uuid_generate_v1();

-- Generate UUID version 1 with custom MAC address
SELECT uuid_generate_v1mc();

-- Generate UUID version 3 - MD5 namespace-based
SELECT uuid_generate_v3(uuid_ns_url(), 'https://example.com');

-- Generate UUID version 4 - random
SELECT uuid_generate_v4();

-- Generate UUID version 5 - SHA-1 namespace-based
SELECT uuid_generate_v5(uuid_ns_url(), 'https://example.com');

Table Schema with UUID Primary Keys

Create table with UUID primary key:

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data (UUID generated automatically)
INSERT INTO users (name, email) 
VALUES ('John Doe', '[email protected]');

-- Query with UUID
SELECT * FROM users WHERE id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';

Using different UUID versions:

-- Table with UUID v1 (time-based)
CREATE TABLE events (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v1(),
    event_type VARCHAR(50),
    data JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table with UUID v5 (deterministic)
CREATE TABLE resources (
    id UUID PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    url VARCHAR(500)
);

-- Insert with deterministic UUID
INSERT INTO resources (id, name, url) 
VALUES (
    uuid_generate_v5(uuid_ns_url(), 'https://example.com/resource'),
    'Example Resource',
    'https://example.com/resource'
);

Advanced PostgreSQL UUID Usage

UUID with indexes for performance:

-- Create table with UUID and proper indexing
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    order_number VARCHAR(50),
    total DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for UUID columns
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- For time-ordered UUIDs, consider using UUID v1 or v6
CREATE TABLE time_series_data (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v1(),
    sensor_id UUID NOT NULL,
    value DECIMAL(10,4),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

UUID validation and conversion:

-- Validate UUID format
SELECT 
    id,
    CASE 
        WHEN id::text ~ '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$' 
        THEN 'Valid UUID'
        ELSE 'Invalid UUID'
    END as validation
FROM users;

-- Convert string to UUID (with error handling)
SELECT 
    CASE 
        WHEN '550e8400-e29b-41d4-a716-446655440000'::uuid IS NOT NULL 
        THEN 'Valid'
        ELSE 'Invalid'
    END;

-- Extract UUID version
CREATE OR REPLACE FUNCTION uuid_version(uuid_val UUID)
RETURNS INTEGER AS $$
BEGIN
    RETURN (('x' || substr(uuid_val::text, 15, 1))::bit(4))::int;
END;
$$ LANGUAGE plpgsql;

SELECT uuid_version(gen_random_uuid());

Performance Considerations

UUID vs SERIAL performance:

-- UUID primary key (better for distributed systems)
CREATE TABLE uuid_table (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    data TEXT
);

-- SERIAL primary key (better for single-node performance)
CREATE TABLE serial_table (
    id SERIAL PRIMARY KEY,
    uuid UUID DEFAULT gen_random_uuid(),
    data TEXT
);

-- Hybrid approach: SERIAL + UUID
CREATE TABLE hybrid_table (
    id SERIAL PRIMARY KEY,
    uuid UUID UNIQUE DEFAULT gen_random_uuid(),
    data TEXT
);

Optimizing UUID storage:

-- Use UUID v1 for time-ordered data (better index performance)
CREATE TABLE logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v1(),
    level VARCHAR(10),
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Partition by UUID prefix for large tables
CREATE TABLE large_dataset (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    data JSONB
) PARTITION BY HASH (id);

CREATE TABLE large_dataset_0 PARTITION OF large_dataset
    FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE large_dataset_1 PARTITION OF large_dataset
    FOR VALUES WITH (modulus 4, remainder 1);

UUID Version Comparison

Choose the right version for your PostgreSQL application:

• Version 1 - Time-based, good for time-series data
• Version 3 - MD5 namespace-based, deterministic
• Version 4 - Random, most popular choice
• Version 5 - SHA-1 namespace-based, more secure than v3
• Version 6 - Time-ordered, better index performance
• Version 7 - Modern time-based (requires custom implementation)

For PostgreSQL applications:

• Web applications: Use Version 4 for user accounts and general entities
• Time-series data: Consider Version 1 for chronological ordering
• Distributed systems: Use Version 5 for deterministic cross-system IDs

How do I generate UUID in other databases?

SQL databases:

• MySQL - Built-in UUID() function
• SQL Server - NEWID() and NEWSEQUENTIALID()
• SQLite - Custom functions and extensions
• Oracle - SYS_GUID() function

NoSQL databases:

• MongoDB - ObjectId and UUID generation
• Redis - UUID generation with Lua scripts
• Cassandra - Built-in UUID functions

← Back to Online UUID Generator