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:
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
-- Enable the uuid-ossp extension for UUID generation CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
2. Insert with a Generated UUID
-- 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:
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:
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4