The following is the syntax of the ‘Upsert’ ON CONFLICT query.
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.
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.
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.
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.
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.
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.
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.
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.
The changes are shown below.
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.’