PostgreSQL – Upsert

The UPSERT statement is a DBMS feature that allows a DML statement’s author to either insert a row or if the row already exists, UPDATE that existing row instead. That is why the action is known as UPSERT (simply a mix of Update and Insert).To achieve the functionality of UPSERT, PostgreSQL uses the INSERT ON CONFLICT statement.

Syntax:

INSERT INTO table_name(column_list)  
VALUES(value_list)
ON CONFLICT target action;

If we analyze the above syntax:

• The target can be :

1. (column_name) – any column name.
2. ON CONSTRAINT constraint_name – where the constraint name could be the name of the UNIQUE constraint.
3.  WHERE predicate  – a WHERE clause with a boolean condition.

• The action can be :

1. DO NOTHING – If the row already exists in the table, then do nothing.
2. DO UPDATE SET columnA = valueA, …. WHERE condition – update some fields in the table depending upon the condition.
    

First, we create a sample table using the below commands to perform examples:

CREATE TABLE employees (
 employee_id serial PRIMARY KEY,
 full_name VARCHAR NOT NULL,
 manager_id INT
);

Then we insert data into our employee table as follows:

INSERT INTO employees (
 employee_id,
 full_name,
 manager_id
)
VALUES
 (1, 'M.S Dhoni', NULL),
 (2, 'Sachin Tendulkar', 1),
 (3, 'R. Sharma', 1),
 (4, 'S. Raina', 1),
 (5, 'B. Kumar', 1),
 (6, 'Y. Singh', 2),
 (7, 'Virender Sehwag ', 2),
 (8, 'Ajinkya Rahane', 2),
 (9, 'Shikhar Dhawan', 2),
 (10, 'Mohammed Shami', 3),
 (11, 'Shreyas Iyer', 3),
 (12, 'Mayank Agarwal', 3),
 (13, 'K. L. Rahul', 3),
 (14, 'Hardik Pandya', 4),
 (15, 'Dinesh Karthik', 4),
 (16, 'Jasprit Bumrah', 7),
 (17, 'Kuldeep Yadav', 7),
 (18, 'Yuzvendra Chahal', 8),
 (19, 'Rishabh Pant', 8),
 (20, 'Sanju Samson', 8);

The table is:

Example 1:

The below query is used to change the name of employee_id 6 (if such an employee exists) from Y. Singh to Yuvraj Singh or insert the record if such an employee does not exist.

insert into employees(employee_id,full_name)
values(6,'Yuvraj Singh')
on conflict(employee_id)
do
update set full_name=EXCLUDED.full_name; 

To check the changes to the table, we use the following query:

select * from employees;

Output:

In this example, we tried to use the UPDATE clause as the action of the INSERT statement to update the name of the employee with ID 6 if the employee exists. 

Example 2:

The below query is used to insert an employee with employee id 18 with the name ‘W. Sundar’ and manager id ‘6’ (if no record exists) but do nothing, in case, such a record is already present in the table.  

insert into employees(employee_id,full_name,manager_id)
values(18,'W. Sundar',6)
on conflict (employee_id)
do nothing;

To check the changes to the table, we use the following query:

select * from employees;

Output:

In this example, nothing was changed in the table because employee with employee id 18 already exists and the action is to do nothing. 

Example 3:

The below query is used to insert an employee with employee id 21 with the name ‘W. Sundar’ and manager id ‘6’ ( if no record exists ) but do nothing, in case, such a record is already present in the table. 

insert into employees(employee_id,full_name,manager_id)
values(21,'W. Sundar',6)
on conflict (employee_id)
do nothing; 

To check the changes to the table, we use the following query:

select * from employees;

Output:

In this example, a new record was inserted into the table as no existing employee was present in the table with employee id 21.