How To Use ON CONFLICT CLAUSE in PostgreSQL
A simple way to upsert data in postgreSQL database
I first found out about ON CONFLICT CLAUSE when I need to insert an array of data to the database, but there is a possibility at least one of the data already exists in the database. I sure could loop the array and check whether the data existed one by one, but that would make the code to be too messy, so I tried to find another option. After a little research, I found there is a technology called ON CONFLICT CLAUSE.
What is ON CONFLICT CLAUSE?
Before we understand what ON CONFLICT CLAUSE means, we need to understand what upsert means. Upsert is the combination of the word update and insert, hence, it is the action of inserting data if the data is nonexistent in the database, and updating it if the opposite thing happens. ON CONFLICT CLAUSE is introduced to PostgreSQL to support the upsert feature.
There are two things you can do with the ON CONFLICT CLAUSE :
- DO NOTHING, which means we are not inserting or updating anything because the data exists in the database table
- DO UPDATE, which means we are updating the preexisting data
In ON CONFLICT query parameter, we can put the column name or the constraint name or a WHERE clause.
First, we need to create an example table to show how it works.
In order to know if a row of data exists in the table, we can use UNIQUE constraint, therefore we add the constraint to the table, and we use name column as the unique one.
We also need to insert some example data
DO NOTHING
In this section, we will see the new data is not inserted since the data with same value in name column already exists.
In the ON CONFLICT query parameter, we use the column name, or you can also the constraint instead, both works.
As you can see in the new data, the name Danny already exists in the table, so no new data is inserted or ignored.
But, if we change the value of the new data in the column name , and leave all the value of the rest of the columns the same, the new data will be inserted
DO UPDATE
in this section, we will the see the old data is updated. In the next query, we will extend the value of the job column of the preexisting data.
In the preexisting data, data with value of the name column Peter already exists, with job value is Front End Engineer and we will add new value to the that column.
We can also increase the value of the old data such as in age column. As you can see in the preexisting data, data with value of the name column Mike already exists, and we will increase the value with the given value in the insert query.
You can also use two or more column in the constraint such as name and country, hence the it will check in those two columns every time you insert new data. if the new data only equal in only one of the column, then it will not fulfill the clause and will insert the new data as it is instead of ignoring or updating the preexisting data.
In this case, when you want to use the ON CONFLICT CLAUSE, you need to use ON CONSTRAINT in the ON CONFLICT query parameter.
I hope you find it useful.
Thanks for reading 😀
Resource :
