How to Use UUID in PostgreSQL: Syntax and Examples

In PostgreSQL, you can use the uuid data type to store universally unique identifiers. Use the uuid-ossp extension to generate UUIDs with functions like uuid_generate_v4(). Define columns as uuid and insert values using these functions or UUID strings.

Syntax

To use UUIDs in PostgreSQL, first enable the uuid-ossp extension. Then define a column with the uuid data type. Use functions like uuid_generate_v4() to create new UUID values.

• CREATE EXTENSION uuid-ossp; - enables UUID functions.
• uuid - column type for UUID values.
• uuid_generate_v4() - generates a random UUID (version 4).

sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE example_table (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  name text NOT NULL
);

Example

This example shows how to create a table with a UUID primary key, insert rows using the uuid_generate_v4() function, and select the data.

sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
  user_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  username text NOT NULL
);

INSERT INTO users (username) VALUES ('alice');
INSERT INTO users (username) VALUES ('bob');

SELECT * FROM users;

Output

user_id                               | username
--------------------------------------+----------
550e8400-e29b-41d4-a716-446655440000 | alice
f47ac10b-58cc-4372-a567-0e02b2c3d479 | bob

Common Pitfalls

Common mistakes when using UUIDs in PostgreSQL include:

• Not enabling the uuid-ossp extension before using UUID functions.
• Trying to insert UUIDs as plain strings without quotes or proper format.
• Using text type instead of uuid, which loses UUID benefits.

Always use the uuid type and generate UUIDs with supported functions.

sql

/* Wrong: inserting UUID as plain string without quotes */
INSERT INTO users (user_id, username) VALUES ('550e8400-e29b-41d4-a716-446655440000', 'charlie');

/* Right: insert UUID as string with quotes */
INSERT INTO users (user_id, username) VALUES ('550e8400-e29b-41d4-a716-446655440000', 'charlie');

Quick Reference

Command	Description
CREATE EXTENSION "uuid-ossp";	Enable UUID generation functions
uuid	Data type for UUID columns
uuid_generate_v4()	Generate a random UUID (version 4)
DEFAULT uuid_generate_v4()	Set default UUID value for a column
'uuid-string'	Insert UUID as a quoted string

Key Takeaways

Enable the uuid-ossp extension to use UUID functions in PostgreSQL.

Use the uuid data type for columns storing UUIDs to ensure proper handling.

Generate UUIDs with uuid_generate_v4() for random unique identifiers.

Always insert UUID values as properly formatted quoted strings or use generation functions.

Avoid using text type for UUIDs to maintain data integrity and indexing benefits.