Performing Inserts and Updates in One Go: PostgreSQL Upsert Explained
PostgreSQL doesn't have a built-in UPSERT (UPDATE or INSERT) statement, but it provides two powerful mechanisms to achieve this functionality:
-
INSERT ... ON CONFLICT
: This is the most common and versatile approach. It allows you to specify what action to take when a conflict occurs during an insert operation (i.e., trying to insert a row that already exists based on a unique constraint or index).-
Syntax:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...) ON CONFLICT ON constraint_name OR index_name DO UPDATE SET column1 = value1, column2 = value2, ... [WHERE condition];
-
Explanation:
INSERT INTO table_name
: Starts the insert operation.(column1, column2, ...)
: Specifies the columns to insert values into.VALUES (value1, value2, ...)
: Provides the values to insert.ON CONFLICT ON constraint_name OR index_name
: Identifies the unique constraint or index that defines the conflict scenario.DO UPDATE SET column1 = value1, column2 = value2, ...
: Defines the update operation to be performed on conflicting rows. You can specify which columns to update and their new values.[WHERE condition] (optional)
: Adds an optional WHERE clause to further refine the update behavior within the conflicting rows.
-
-
MERGE
Statement (PostgreSQL 15 and later): This is a newer addition that offers a more concise syntax for upserts:-
MERGE INTO table_name USING ( SELECT column1, column2, ... FROM source_table ) AS target ON target.unique_column = table_name.unique_column WHEN MATCHED THEN UPDATE SET column1 = target.column1, column2 = target.column2, ... WHEN NOT MATCHED THEN INSERT (column1, column2, ...) VALUES (target.column1, target.column2, ...);
-
USING (SELECT ... FROM source_table) AS target
: Defines a virtual table (target
) containing the data to be inserted or updated.ON target.unique_column = table_name.unique_column
: Specifies the join condition for matching rows.WHEN MATCHED THEN UPDATE ...
: Defines the update operation for matching rows.
-
Choosing the Right Approach:
- Use
INSERT ... ON CONFLICT
for more granular control over conflict resolution and update behavior. - If you're using PostgreSQL 15 or later and prefer a more streamlined syntax,
MERGE
can be a good option.
Example (Using INSERT ... ON CONFLICT
):
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE
);
INSERT INTO users (username) VALUES ('john')
ON CONFLICT (username) DO UPDATE SET username = 'john_updated';
In this example:
- If
username
'john' doesn't exist, it will be inserted. - If
username
'john' already exists, it will be updated to 'john_updated'.
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(50) UNIQUE,
price DECIMAL(10,2) NOT NULL
);
-- Insert a new product (assuming 'name' 'Widget' doesn't exist)
INSERT INTO products (name, price) VALUES ('Widget', 19.99)
ON CONFLICT (name) DO UPDATE SET price = EXCLUDED.price; -- Update price using EXCLUDED
-- Insert another product, updating price if 'name' 'Gadget' already exists
INSERT INTO products (name, price) VALUES ('Gadget', 24.50)
ON CONFLICT (name) DO UPDATE SET price = EXCLUDED.price;
This example demonstrates:
- Inserting new products with unique names (
name
). - Updating the price of an existing product (
Gadget
) if the name conflicts. - Using
EXCLUDED
to access the values being attempted for insertion during conflict.
Example 2: Upsert with Additional Check (Update Only if Existing Price is Lower)
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
salary DECIMAL(10,2) NOT NULL
);
-- Insert or update employee salary, but only if new salary is higher
INSERT INTO employees (name, salary) VALUES ('Alice', 80000.00)
ON CONFLICT (name) DO UPDATE SET salary = EXCLUDED.salary
WHERE employees.salary < EXCLUDED.salary; -- Update only if existing < new
This example shows:
- Upserting an employee's salary (
Alice
). - Updating the salary only if the new salary (
80000.00
) is higher than the existing one using an additionalWHERE
clause.
Using MERGE Statement (PostgreSQL 15 and later):
Example:
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
email VARCHAR(100) UNIQUE,
name VARCHAR(50) NOT NULL
);
-- Create a temporary table to hold data for upsert
CREATE TEMP TABLE customer_data (
email VARCHAR(100) PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
-- Insert data into temporary table
INSERT INTO customer_data (email, name) VALUES ('[email protected]', 'John Doe');
MERGE INTO customers USING customer_data AS target
ON target.email = customers.email
WHEN MATCHED THEN UPDATE SET name = target.name
WHEN NOT MATCHED THEN INSERT (email, name) VALUES (target.email, target.name);
DROP TABLE customer_data; -- Cleanup temporary table
- Using a temporary table (
customer_data
) to stage data for upsert. - Performing a merge operation using
MERGE
. - Updating existing customer names (
John Doe
) if email matches. - Inserting new customers if email doesn't exist.
- Dropping the temporary table after use.
This method involves attempting an INSERT
and then checking if it succeeded (no unique constraint violation). If it fails due to a conflict, you can perform an UPDATE
in a loop until successful. However, this is less efficient and can lead to race conditions in high-concurrency scenarios.
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE
);
LOOP
INSERT INTO accounts (username) VALUES ('new_user') RETURNING id; -- Check for success
EXIT WHEN FOUND; -- Exit if insert succeeded
END LOOP;
IF NOT FOUND THEN -- Update if insert failed (assuming unique username conflict)
UPDATE accounts SET username = 'new_user_updated'
WHERE username = 'new_user';
END IF;
User-Defined Functions (UDFs):
You can create a PL/pgSQL function that encapsulates the upsert logic using INSERT ... ON CONFLICT
or a similar approach internally. This can improve code organization, but the underlying functionality remains the same as INSERT ... ON CONFLICT
.
ORM or Object-Relational Mapper Tools:
Many ORMs (Object-Relational Mappers) like SQLAlchemy or Django's ORM provide built-in methods for upserts. These methods typically translate to INSERT ... ON CONFLICT
under the hood, offering a more convenient API for your application code.
Choosing the Best Method:
- For most cases,
INSERT ... ON CONFLICT
is the preferred approach due to its efficiency and built-in conflict resolution handling. - Use
MERGE
(if your PostgreSQL version supports it) for a more concise syntax. - Consider separate
INSERT
andUPDATE
statements only if you have very specific requirements or cannot use the recommended methods. - If you're using an ORM, leverage its upsert functionality for a higher-level abstraction.
postgresql upsert