How to Use uuid-ossp Extension in PostgreSQL for UUID Generation

To use the uuid-ossp extension in PostgreSQL, first enable it with CREATE EXTENSION IF NOT EXISTS "uuid-ossp";. Then you can generate UUIDs using functions like uuid_generate_v4() in your SQL queries or table defaults.

Syntax

The uuid-ossp extension provides functions to generate UUIDs. You enable it with:

• CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; - activates the extension in your database.
• Functions like uuid_generate_v1(), uuid_generate_v4() generate UUIDs of different versions.
• You can use these functions in INSERT statements or as default values for columns.

sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

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

Output

               uuid_generate_v4               
----------------------------------------------
 550e8400-e29b-41d4-a716-446655440000
(1 row)

Example

This example shows how to enable the extension, create a table with a UUID primary key, and insert a row using uuid_generate_v4() as the default value.

sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name TEXT NOT NULL
);

INSERT INTO users (name) VALUES ('Alice');

SELECT * FROM users;

Output

                  id                  | name  
--------------------------------------+-------
 3f29a8f2-8d4e-4f3a-9f4a-2b8a1c6e7d3e | Alice
(1 row)

Common Pitfalls

Common mistakes when using uuid-ossp include:

• Not enabling the extension before using its functions, causing errors.
• Forgetting to quote the extension name in CREATE EXTENSION, which is case-sensitive.
• Using UUID functions without specifying them as defaults, requiring manual UUID generation on inserts.

sql

/* Wrong: extension not enabled */
-- SELECT uuid_generate_v4();
-- ERROR: function uuid_generate_v4() does not exist

/* Correct: enable extension first */
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();

Output

               uuid_generate_v4               
----------------------------------------------
 550e8400-e29b-41d4-a716-446655440000
(1 row)

Quick Reference

Command/Function	Description
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";	Enable the uuid-ossp extension in your database
uuid_generate_v1()	Generate a UUID based on timestamp and MAC address
uuid_generate_v4()	Generate a random UUID (most common)
uuid_generate_v5(namespace, name)	Generate a UUID based on SHA-1 hash of namespace and name
Use UUID columns with DEFAULT uuid_generate_v4()	Automatically assign UUIDs on insert

Key Takeaways

Always enable the uuid-ossp extension before using its UUID functions.

Use uuid_generate_v4() to create random UUIDs easily in PostgreSQL.

Set UUID columns with DEFAULT uuid_generate_v4() to auto-generate IDs on insert.

Remember to quote "uuid-ossp" in CREATE EXTENSION as it is case-sensitive.

Without enabling the extension, UUID functions will not be recognized.