Generate UUID in PostgreSQL, MySQL, and SQL Server

Every major database can generate UUIDs natively — no application-layer generation required. PostgreSQL's gen_random_uuid(), MySQL's UUID(), and SQL Server's NEWID() are all production-ready. The differences matter for index performance and storage efficiency. Here is the complete reference for each database.

PostgreSQL — gen_random_uuid() and uuid-ossp

-- PostgreSQL 13+: gen_random_uuid() is built-in (no extension needed)
SELECT gen_random_uuid();
-- Returns: 550e8400-e29b-41d4-a716-446655440000

-- PostgreSQL 12 and earlier: use uuid-ossp extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();

-- Create table with UUID primary key (auto-generated)
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Insert without specifying ID
INSERT INTO users (email) VALUES ('[email protected]');

-- Insert with explicit UUID
INSERT INTO users (id, email)
VALUES (gen_random_uuid(), '[email protected]');

-- Retrieve as string
SELECT id::text FROM users;

-- Generate multiple UUIDs
SELECT gen_random_uuid() FROM generate_series(1, 10);

PostgreSQL stores UUID natively as a 16-byte value — not as a 36-char string. This means indexes on UUID columns are compact and efficient. Always use the native UUID type rather than VARCHAR(36).

MySQL — UUID() and Efficient Binary Storage

-- Basic UUID generation
SELECT UUID();
-- Returns: '550e8400-e29b-41d4-a716-446655440000' (text format)

-- Efficient storage: BINARY(16) with UUID_TO_BIN
-- The '1' flag reorders bytes for sequential ordering (better for indexes)
CREATE TABLE orders (
    id BINARY(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID(), 1)),
    status VARCHAR(50),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

-- Insert
INSERT INTO orders (status) VALUES ('pending');

-- Read back as string
SELECT BIN_TO_UUID(id, 1) AS id, status FROM orders;

-- If you prefer text storage (simpler but larger)
CREATE TABLE orders_text (
    id CHAR(36) NOT NULL DEFAULT (UUID()),
    PRIMARY KEY (id)
);

-- MySQL 8.0+ only: generated column approach
ALTER TABLE orders ADD COLUMN id_str CHAR(36)
    GENERATED ALWAYS AS (BIN_TO_UUID(id, 1)) VIRTUAL;

The UUID_TO_BIN(UUID(), 1) flag reorders the UUID bytes so the time component comes first — effectively giving you the sequential ordering of UUID v6/v7 within MySQL's binary representation. This dramatically reduces index fragmentation on high-insert tables.

Sell Custom Apparel — We Handle Printing & Free Shipping

SQL Server — NEWID() vs NEWSEQUENTIALID()

-- Basic UUID generation (random)
SELECT NEWID();
-- Returns: 550E8400-E29B-41D4-A716-446655440000

-- Create table with random GUID
CREATE TABLE Products (
    ProductId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    Name NVARCHAR(100) NOT NULL
);

-- NEWSEQUENTIALID() — sequential GUIDs, prevents fragmentation
-- Can only be used as DEFAULT constraint, not in SELECT
CREATE TABLE Orders (
    OrderId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(),
    CustomerId UNIQUEIDENTIFIER NOT NULL,
    CreatedAt DATETIME2 DEFAULT GETUTCDATE()
);

-- Generate UUID for use in a query
DECLARE @NewId UNIQUEIDENTIFIER = NEWID();
INSERT INTO Products (ProductId, Name)
VALUES (@NewId, 'Widget');

-- Convert to string
SELECT CAST(NEWID() AS VARCHAR(36));
SELECT CONVERT(VARCHAR(36), NEWID());

-- Without dashes
SELECT REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', '');

Use NEWSEQUENTIALID() for clustered primary keys — it generates monotonically increasing GUIDs that prevent page fragmentation. Use NEWID() for non-clustered columns or when you need randomness. SQL Server's UNIQUEIDENTIFIER type stores the GUID as 16 bytes.

Cross-Database UUID Best Practices

Storage type matters most

Database	Recommended Type	Storage	Notes
PostgreSQL	UUID	16 bytes	Native type, efficient indexes
MySQL 8+	BINARY(16)	16 bytes	Use UUID_TO_BIN/BIN_TO_UUID helpers
MySQL (any)	CHAR(36)	36 bytes	Simpler but larger — avoid for high-volume tables
SQL Server	UNIQUEIDENTIFIER	16 bytes	Native type, use NEWSEQUENTIALID for clustered PK
SQLite	TEXT	36 bytes	No native UUID type — store as text or BLOB

Index performance: Random UUID v4 causes page fragmentation in clustered indexes. Solutions: use PostgreSQL's UUID type (btree handles it reasonably), MySQL's UUID_TO_BIN(UUID(), 1), SQL Server's NEWSEQUENTIALID(), or upgrade to UUID v7 which is time-ordered.

When you need to manually generate a UUID to test a query or seed test data, the WildandFree UUID Generator gives you one instantly — paste directly into your SQL editor.

Frequently Asked Questions

What is the difference between gen_random_uuid() and uuid_generate_v4() in PostgreSQL?

Both generate UUID v4. gen_random_uuid() is built into PostgreSQL 13+ (no extension needed) and is slightly faster. uuid_generate_v4() comes from the uuid-ossp extension and is available in all PostgreSQL versions. For new projects on PostgreSQL 13+, use gen_random_uuid().

Should I store UUIDs as VARCHAR or use native UUID types?

Always use native types where available. PostgreSQL's UUID type stores 16 bytes. VARCHAR(36) stores 36 bytes — 2.25x larger — and makes indexes correspondingly larger. MySQL has no native UUID type but BINARY(16) with UUID_TO_BIN is the right approach. SQL Server's UNIQUEIDENTIFIER is 16 bytes.

Why does MySQL recommend UUID_TO_BIN(UUID(), 1) instead of just UUID()?

UUID() returns a text string that takes 36 bytes in CHAR/VARCHAR columns. UUID_TO_BIN converts it to 16 bytes for BINARY(16) storage. The "1" flag swaps the time-related components so the bytes are in sequential order, reducing B-tree index fragmentation. It is the MySQL equivalent of UUID v6 ordering.

Can I generate UUID v7 directly in PostgreSQL?

PostgreSQL 17 adds uuidv7() as a built-in function. For earlier versions, use an extension like pg_uuidv7 (available via PGXS) or generate v7 UUIDs at the application level and insert them. UUID v7 is time-ordered, which eliminates the index fragmentation issue of random v4 keys.