PostgreSQL

PostgreSQL Upsert on Conflict

2 years ago
by Aqsa Yasin
The ‘Upsert’ expression is recognized as a merge in RDBMS. An ‘Upsert’ is helpful to have with PostgreSQL if you do not want to deal with unique restriction violation errors due to repeated entries. When you run a generic UPDATE command, the necessary adjustments will only occur if a record fits the command standards; if no such data exists, then nothing happens. When you add a new row to a table, PostgreSQL will update the row if it existed previously, or else it will incorporate the new row if the row did not exist. The ‘Upsert’ SQL keyword combines the words “update” and “insert.” This article shows you how the PostgreSQL ‘Upsert’ feature works, as well as how to use the ‘Upsert’ feature to add or update information if the inserted row already exists in the table.

Syntax

The following is the syntax of the ‘Upsert’ ON CONFLICT query.

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

Launch PostgreSQL Command-Line Shell

To begin, launch the PostgreSQL command-line shell from the Application menu. Connect it to the required server. Enter the database name that you want to work on. To work on another port, type the port number and the username that you want to work with. To continue with the default parameters, leave the spaces as is and press ‘Enter’ at every option. Add a password for the selected username, and your command-shell should be ready to use.

Example 1:

Now, we can get started with ‘Upsert’ in conflict. Suppose you have a table titled ‘person’ in a selected database with some fields that show records of different persons. These records show the names of the people, their ages, and their cities and countries. The table is displayed below.

>> SELECT * FROM person;

It is important to know how an error or conflict can arise. The ‘id’ table field, which is also a primary key, contains values from 1 to 15. When the user tries to insert some duplicate records into the table, the conflict occurs.

Let us try the following INSERT statement, inserting the records into the ‘person’ table. This query will cause an error because the value ‘3’ of the ‘id’ field already exists in the table.

>> INSERT INTO person (id, name, age, city, country) VALUES ( ‘3’, ‘Habib’, ‘45’, ‘Chakwal’, ‘Pakistan’);

Example 2: Upsert With ON CONFLICT Clause

We will use the ON CONFLICT clause to avoid the INSERT query that is causing this error due to inserting the duplicate records. The ON CONFLICT command comes up with two phrases with different usages.

  • DO: Perform operations to overcome the conflict.
  • DO NOTHING: Avoid the conflict without doing anything.

Example 3: Upsert With DO NOTHING Clause

In this example, we will look at the DO NOTHING clause. This clause elaborates that no operation will be performed upon error or conflict. In other words, this clause will only avoid conflict or error.

So, let us try the same INSERT command that we used earlier to add duplicate records to the ‘person’ table, with a few changes appended. We have added the ON CONFLICT clause, along with the DO NOTHING statement in this clause. The ON CONFLICT clause has been applied to the unique ‘id’ column. This means that, when the user attempts to insert the duplicate value to the ‘id’ column, it will avoid the conflict and do nothing. As you can see in the image below, it will not even insert the new record into the table, nor will it update the previous record.

>> INSERT INTO person (id, name, age, city, country) VALUES ( ‘3’, ‘Habib’, ‘45’, ‘Chakwal’, ‘Pakistan’) ON CONFLICT (id) DO NOTHING;

Let us check the ‘person’ table again for the sake of authenticity. As you can see in the image below, no changes have been made to the table.

>> SELECT * FROM person;

Example 2: Upsert With DO Clause

Next, we will look at the ON CONFLICT and DO clauses. As its name indicates, the ___ clause will perform an action upon error or conflict when a duplicate value is inserted into a table. We will use the same insert command that we used previously to insert a duplicate record in the ‘person’ table, with a minor alteration. We have added the ON CONFLICT clause with the DO clause inside of it. When the user attempts to insert the non-unique value to the ‘id’ column, it will perform an action to avoid the conflict. We have been using the UPDATE clause after the DO clause, which indicates an update to the data in the ‘person’ table. The SET keyword is used to set the value of the ‘name’ column to the new value, ‘Habib,’ using the keyword EXCLUDED where the ‘id’ is ‘3’ at the current time. If you execute the following query, you will see that the query has been performed.

>> INSERT INTO person (id, name, age, city, country) VALUES ( ‘3’, ‘Habib’, ‘45’, ‘Chakwal’, ‘Pakistan’) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

The records of the ‘person’ table must be fetched to see the changes to the above query. If you execute the following query in the command-line shell, you should see the subsequent output.

>> SELECT * FROM person;

As you can see from the output below, the name of the person has been updated to ‘Habib,’ where the ‘id’ is ‘3.’

You can also update the records in more than one column using the EXCLUDED keyword in the ON CONFLICT clause of the INSERT query, as shown below.

>> INSERT INTO person (id, name, age, city, country) VALUES ( ‘3’, ‘Habib’, ‘45’, ‘Chakwal’, ‘Pakistan’) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, city = EXCLUDED.city;

The changes are shown below.

>> SELECT * FROM person;

Conclusion

This article showed you how to use PostgreSQL ‘Upsert’ with the ON CONFLICT clause, along with the DO and DO NOTHING actions. After reading this article, we hope that you find it easier to understand how to use PostgreSQL ‘Upsert.’

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.

View all posts