How to Generate UUID in PostgreSQL: Syntax and Examples

In PostgreSQL, you can generate a UUID using the gen_random_uuid() function from the pgcrypto extension or the uuid_generate_v4() function from the uuid-ossp extension. First, enable the extension with CREATE EXTENSION, then call the function to get a new UUID.

Syntax

To generate a UUID, you first need to enable an extension that provides UUID functions. The two common extensions are pgcrypto and uuid-ossp. After enabling, you call the function to get a UUID.

• CREATE EXTENSION pgcrypto; enables gen_random_uuid().
• CREATE EXTENSION "uuid-ossp"; enables uuid_generate_v4().
• Use SELECT gen_random_uuid(); or SELECT uuid_generate_v4(); to generate a UUID.

sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- or
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

SELECT gen_random_uuid();
-- or
SELECT uuid_generate_v4();

Example

This example shows how to enable the pgcrypto extension and generate a UUID using gen_random_uuid(). It demonstrates the full process from setup to UUID generation.

sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

SELECT gen_random_uuid() AS new_uuid;

Output

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

Common Pitfalls

Common mistakes include not enabling the required extension before calling the UUID function, which causes errors. Also, confusing the two extensions or their functions can lead to unexpected results. Always check if the extension is installed and enabled.

Wrong way (missing extension):

SELECT gen_random_uuid();

This will cause an error if pgcrypto is not enabled.

Right way:

CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT gen_random_uuid();

Quick Reference

Command	Description
CREATE EXTENSION IF NOT EXISTS pgcrypto;	Enables gen_random_uuid() function
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";	Enables uuid_generate_v4() function
SELECT gen_random_uuid();	Generates a random UUID (version 4) using pgcrypto
SELECT uuid_generate_v4();	Generates a random UUID (version 4) using uuid-ossp

Key Takeaways

Enable the required extension before generating UUIDs in PostgreSQL.

Use gen_random_uuid() from pgcrypto or uuid_generate_v4() from uuid-ossp to generate UUIDs.

Always check if the extension is installed to avoid errors.

UUIDs generated are version 4 (random).

Use CREATE EXTENSION IF NOT EXISTS to safely enable extensions.