PostgreSQL – UPSERT

An “upsert” operation refers to the ability to update an existing row if it exists, or insert a new row if it doesn’t exist, based on a specified condition. The upsert operation is also known as “merge” or “insert on conflict update.”

INSERT INTO target_table (column1, column2, ...) 
VALUES (value1, value2, ...) 
ON CONFLICT (unique_constraint_columns) 
DO UPDATE SET column1 = new_value1, column2 = new_value2, ...;

where target_table is the table where you want to perform the upsert operation.

(column1, column2, ...) are the columns you want to insert values into.

VALUES (value1, value2, ...) are the values you want to insert.

ON CONFLICT (unique_constraint_columns) specifies the conflict resolution behavior based on the unique constraint columns. If a conflict occurs (i.e., a row with the same unique constraint already exists), the DO UPDATE part is executed.

DO UPDATE SET column1 = new_value1, column2 = new_value2, ... specifies the columns you want to update and their new values in case of a conflict.

Below is an example:

Assuming you have a table employees with a unique constraint on the employee_id column:

CREATE TABLE employees 
( employee_id serial PRIMARY KEY, 
first_name text, 
last_name text, 
salary numeric ); 

ALTER TABLE employees ADD CONSTRAINT unique_employee_id UNIQUE (employee_id);

You can perform an upsert operation like this:

INSERT INTO employees (employee_id, first_name, last_name, salary) 
VALUES (1, 'John', 'Doe', 50000) 
ON CONFLICT (employee_id) 
DO UPDATE SET salary = EXCLUDED.salary;

In this example, if an employee with employee_id 1 already exists, the salary will be updated to 50000. If not, a new row with employee_id 1 will be inserted with the provided values.