PostgreSQL offers an Upsert feature that allows us to execute an insert or update operation. In other databases, the upsert feature is known as merge. This is because the upsert feature combines update and insert queries, and hence using the upsert feature, you can update an already existing record, or you can insert a new record into the targeted table.

In PostgreSQL, the upsert feature can be implemented with the aid of the INSERT ON CONFLICT statement.

This write-up will show you how to perform insert or update operations using the Postgres upsert feature with examples. So, let’s start!

How to Use INSERT ON CONFLICT Statement in Postgres?

Here is the syntax of the INSERT ON CONFLICT statement:

INSERT INTO tab_name(col_1, col_2,..., col_N) 
VALUES(val_1, val_2,..., val_N)
ON CONFLICT target action;

Let’s describe the above-given query step-by-step:

- “INSERT INTO” is a query used along with the “ON CONFLICT target action” clause to insert or update the table’s data.

- In place of “target”, you can specify a column name, a unique constraint using ON CONSTRAINT, or a WHERE clause with a predicate.

- In Place of the “action”, you can specify DO NOTHING or DO UPDATE; the DO UPDATE clause will modify some specific fields in the given table based on the condition.

Example #1: INSERT ON CONFLICT DO NOTHING

To understand the working of upsert, follow the below-given step_wise guidelines:

Step # 1: Create Table

Firstly, create a table named emp_details:

CREATE TABLE emp_data(
emp_id INT UNIQUE,
emp_name TEXT,
emp_email VARCHAR NOT NULL);

The above-given query will create a table named emp_data with three columns: emp_id, emp_name, and emp_email.

- The emp_id column will accept a unique integer value.

- emp_name column will accept the string type data.

- emp_email column will accept only unique emails because we utilized the UNIQUE constraints for the emp_email column.

A table named emp_data has been created successfully.

Step #2: Insert Data

Let’s insert some data into the emp_data table using the INSERT command:

INSERT INTO emp_data(emp_id, emp_name, emp_email)
VALUES (1, 'Joe', 'joe123@abc.com'),
(13, 'John', 'john123@abc.com'),
(10, 'Mike', 'mike123@abc.com'),
(7, 'Seth', 'seth123@abc.com'),
(3, 'Bob', 'bob123@abc.com');

Five rows have been inserted into the emp_data table.

Step #3: Understanding DO NOTHING

Specifying the “DO NOTHING action” within INSERT ON CONFLICT statement will perform one of two functionalities:

- If the specified record doesn’t exist in the table, then the INSERT ON CONFLICT will insert that record into the targeted table.

- If the specified record already exists in the table, then the INSERT ON CONFLICT statement will ignore that record and do nothing.

Scenario #1: Record Doesn’t Exist:

Here's how the INSERT ON CONFLICT statement works if the specified record does not exist in the target table:

INSERT INTO emp_data(emp_id, emp_name, emp_email)
VALUES(2, 'ambrose', 'ambrose21@xyz.com') 
ON CONFLICT (emp_id) 
DO NOTHING;

The output clarifies that one record has been inserted into the emp_data table.

Scenario #2: Record Already Exist:

Suppose “Bob” wants to change his email address from “bob123@abc.com” to “bob321@xyz.com”. Let’s use the upsert feature using “INSERT ON CONFLICT” as follows:

INSERT INTO emp_data(emp_id, emp_email)
VALUES(3, 'bob321@xyz.com') 
ON CONFLICT (emp_id) 
DO NOTHING;

In this example, we specified “emp_id” in the ON CONFLICT clause and “DO NOTHING” in place of action. So, if the specified emp_id already exists in the given table, then the “INSERT ON CONFLICT” statement will do nothing.

In the INSERT ON CONFLICT command, specifying the “DO NOTHING” action ignored the specified value and did nothing.

Learning Outcomes:

From the respective outputs, we can conclude that if you specify the “DO NOTHING” action in the “INSERT ON CONFLICT” statement, then:

- Either the specified record will be inserted(if it does not exist in the table) into the targeted table.

- Or the INSERT ON CONFLICT statement will ignore the specified record(if a record already exists).

- The record that already exists wouldn’t be updated.

Example #2: INSERT ON CONFLICT DO UPDATE

To update the existing data, specify the “DO UPDATE” in place of action in the INSERT ON CONFLICT statement:

INSERT INTO emp_data(emp_id, emp_name, emp_email)
VALUES(3, 'bob', 'bob321@xyz.com') 
ON CONFLICT (emp_id) 
DO UPDATE SET emp_email = EXCLUDED.emp_email;

Run the SELECT statement to see the updated record:

SELECT * FROM emp_data;

The output shows that specifying the DO UPDATE action within the INSERT ON CONFLICT statement updated the selected record.

Conclusion

In PostgreSQL, the upsert feature is used either to update the already existing records or to insert new records into a table. The upsert feature is implemented using the INSERT ON CONFLICT statement. Within the INSERT ON CONFLICT statement, the action determines what to do, i.e. either update the record or insert the new record. This write-up taught us how to use the upsert feature using the INSERT ON CONFLICT statement.