How to Create UUID in PostgreSQL 13+ (Recommended)
PostgreSQL 13 and later includes gen_random_uuid() as a built-in function. No extensions required:
SQL
-- Generate a random UUID v4
SELECT gen_random_uuid();
-- Result: 550e8400-e29b-41d4-a716-446655440000
-- 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 without specifying ID (auto-generated)
INSERT INTO users (name, email)
VALUES ('John Doe', 'john@example.com');
-- Query by UUID
SELECT * FROM users
WHERE id = '550e8400-e29b-41d4-a716-446655440000';
-- Index on UUID column
CREATE INDEX idx_users_id ON users(id);PostgreSQL 13+: gen_random_uuid() is built-in and ready to use. This is the recommended approach for all new applications.
How to Create UUID with uuid-ossp Extension
For PostgreSQL versions before 13, or when you need UUID v1 or v5, use the uuid-ossp extension:
SQL
-- Enable the uuid-ossp extension (one-time setup)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- Generate UUID v4 (random)
SELECT uuid_generate_v4();
-- Generate UUID v1 (timestamp + MAC address)
SELECT uuid_generate_v1();
-- Generate UUID v1mc (timestamp + random MAC for privacy)
SELECT uuid_generate_v1mc();
-- Generate UUID v5 (SHA-1 hash, deterministic)
SELECT uuid_generate_v5(uuid_ns_dns(), 'example.com');
-- Available namespaces for v5
SELECT uuid_ns_dns(); -- DNS namespace
SELECT uuid_ns_url(); -- URL namespace
SELECT uuid_ns_oid(); -- ISO OID namespace
SELECT uuid_ns_x500(); -- X.500 DN namespace
-- Create table using uuid-ossp
CREATE TABLE products (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
name VARCHAR(200) NOT NULL,
sku VARCHAR(50) UNIQUE
);How to Create Time-Sortable UUID v7 in PostgreSQL
PostgreSQL doesn't have native UUID v7 support yet. Create a custom function for time-ordered UUIDs that improve B-tree index performance:
SQL - UUID v7 Function
-- Create UUID v7 generator function
CREATE OR REPLACE FUNCTION uuid_generate_v7()
RETURNS uuid AS $$
DECLARE
unix_ts_ms BIGINT;
uuid_bytes BYTEA;
BEGIN
-- Get current timestamp in milliseconds
unix_ts_ms = (EXTRACT(EPOCH FROM clock_timestamp()) * 1000)::BIGINT;
-- Construct UUID bytes
uuid_bytes = substring(int8send(unix_ts_ms) from 3);
uuid_bytes = uuid_bytes || gen_random_bytes(10);
-- Set version (7) and variant bits
uuid_bytes = set_byte(uuid_bytes, 6, (get_byte(uuid_bytes, 6) & 15) | 112);
uuid_bytes = set_byte(uuid_bytes, 8, (get_byte(uuid_bytes, 8) & 63) | 128);
RETURN encode(uuid_bytes, 'hex')::uuid;
END;
$$ LANGUAGE plpgsql VOLATILE;
-- Use UUID v7 in table
CREATE TABLE orders (
id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
customer_id UUID NOT NULL,
total DECIMAL(10,2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Generate UUID v7
SELECT uuid_generate_v7();Performance Benefit: UUID v7 UUIDs are time-sortable, reducing B-tree index fragmentation by 2-10x compared to random UUID v4. Ideal for high-insert tables.
UUID Data Type and Storage
PostgreSQL's native UUID type stores values as 16 bytes, not as 36-character strings:
SQL - Storage Comparison
-- UUID type (16 bytes) - RECOMMENDED
CREATE TABLE items (
id UUID PRIMARY KEY,
name VARCHAR(100)
);
-- String type (36 bytes) - NOT RECOMMENDED
CREATE TABLE items_bad (
id VARCHAR(36) PRIMARY KEY,
name VARCHAR(100)
);
-- Check column size
SELECT
pg_column_size(id) as uuid_bytes,
pg_column_size(id::text) as string_bytes
FROM items
LIMIT 1;
-- Result: uuid_bytes=16, string_bytes=36
-- UUID input formats (all valid)
INSERT INTO items (id, name) VALUES
('550e8400-e29b-41d4-a716-446655440000', 'Standard'),
('{550e8400-e29b-41d4-a716-446655440000}', 'Braces'),
('550e8400e29b41d4a716446655440000', 'No hyphens');Storage Tip: Always use the UUID data type, not VARCHAR. This saves 55% storage space and significantly improves index performance.
Indexing UUIDs for Performance
PostgreSQL supports efficient indexing of UUID columns:
SQL - Indexing
-- B-tree index (default, supports <, >, =, <=, >=)
CREATE INDEX idx_users_id ON users(id);
-- Hash index (faster for equality lookups only)
CREATE INDEX idx_users_id_hash ON users USING hash(id);
-- Composite index with UUID
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);
-- Partial index (index only active users)
CREATE INDEX idx_active_users
ON users(id) WHERE active = true;
-- Check index usage
SELECT
schemaname,
tablename,
indexname,
idx_scan as index_scans,
pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE tablename = 'users';
-- Analyze query performance
EXPLAIN ANALYZE
SELECT * FROM users WHERE id = gen_random_uuid();UUID vs SERIAL Comparison
Choosing between UUID and auto-incrementing integers (SERIAL/BIGSERIAL):
| Aspect | UUID | SERIAL/BIGSERIAL |
|---|---|---|
| Storage Size | 16 bytes | 4/8 bytes |
| Globally Unique | ✓ Across all tables/databases | ✗ Per-table only |
| Distributed Systems | ✓ No coordination needed | ✗ Requires sequence coordination |
| Security | ✓ Non-guessable | ✗ Sequential, predictable |
| Index Performance | Good (v7 excellent) | ✓ Excellent |
| URL Friendliness | Long URLs | ✓ Short URLs |
| Best For | APIs, microservices, multi-DB | Single-database apps |
ORM Integration Examples
Using PostgreSQL UUIDs with popular frameworks:
Django (Python)
Python - Django
from django.db import models
import uuid
class User(models.Model):
id = models.UUIDField(
primary_key=True,
default=uuid.uuid4,
editable=False
)
name = models.CharField(max_length=100)
email = models.EmailField(unique=True)
created_at = models.DateTimeField(auto_now_add=True)SQLAlchemy (Python)
Python - SQLAlchemy
from sqlalchemy import Column, String, DateTime
from sqlalchemy.dialects.postgresql import UUID
import uuid
class User(Base):
__tablename__ = 'users'
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
name = Column(String(100), nullable=False)
email = Column(String(255), unique=True, nullable=False)
created_at = Column(DateTime, server_default='now()')