How to generate a UUID in PostgreSQL for insert statements?

Generating a UUID in PostgreSQL for an insert statement

In PostgreSQL, a UUID (Universally Unique Identifier) is often used as a primary key or unique identifier due to its globally unique property. PostgreSQL has built-in support for UUIDs, making it easy to generate them within an INSERT statement. Using a UUID provides a unique, non-sequential identifier that is useful in distributed systems or when a unique identifier is required.

Enabling the uuid-ossp Extension

To generate UUIDs in PostgreSQL, you need to enable the uuid-ossp extension, which provides functions for UUID generation. Run the following command to enable it:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Syntax for Generating a UUID during insert:

With the uuid-ossp extension enabled, you can use the uuid_generate_v4() function to generate a random UUID in your INSERT statement.

INSERT INTO table_name (id, column1, column2, ...)
VALUES (uuid_generate_v4(), value1, value2, ...);

Explanation:

• table_name: Name of the table where you are inserting data.
• uuid_generate_v4(): Generates a random UUID.
• (column1, column2, ...): Columns in the table.
• (value1, value2, ...): Values corresponding to each column.

Example Code and Explanation:

Suppose you have a table called products, where product_id is the UUID primary key. Here’s how you can insert a new product with an auto-generated UUID.

1. Enable the uuid-ossp Extension

Code:

-- Enable the uuid-ossp extension for UUID generation
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

2. Insert with a Generated UUID

Code:

-- Insert a new row with a randomly generated UUID for product_id
INSERT INTO products (product_id, name, price)
VALUES (uuid_generate_v4(), 'Gadget Pro', 299.99);

Explanation:

• CREATE EXTENSION IF NOT EXISTS "uuid-ossp": Ensures the UUID extension is enabled.
• INSERT INTO products (product_id, name, price): Specifies the table and columns for the insert.
• uuid_generate_v4(): Generates a unique UUID for the product_id.
• 'Gadget Pro', 299.99: Sample values for name and price.

Using UUIDs with Default Values

You can also set a UUID as the default value for a column in table creation, ensuring every row gets a unique identifier automatically.

Code:

-- Create a table with a UUID as the default for product_id
CREATE TABLE orders (
    order_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    customer_name VARCHAR(100),
    total_amount DECIMAL(10, 2)
);
In this case, you can insert data without specifying the order_id, and it will automatically generate a UUID.
-- Insert a new order with auto-generated UUID for order_id
INSERT INTO orders (customer_name, total_amount)
VALUES ('Jane Doe', 500.00);

Important Notes:

• UUID Version: uuid_generate_v4() creates random UUIDs (version 4). PostgreSQL also supports other UUID versions for specific use cases.
• Performance: UUIDs are ideal for distributed systems but can impact indexing performance compared to sequential integers.

All PostgreSQL Questions, Answers, and Code Snippets Collection.