PostgreSQL – Insert on conflict(Upsert)

 

With PostgreSQL, it is very easy to manage the case “update if there is a record, if not, add”. In traditional methods, we first check whether a record with a SELECT statement is in the table, and then run the INSERT or UPDATE statement as the case.

In PostgreSQL, we can resolve this situation with a single INSERT statement.

In addition, we get better performance than the traditional method.

Create a table to see the usage.

 

As you can see, we don’t have a primary key in the table.

In order to use INSERT .. ON conflict .., the column in the conflict section must contain a primary key or unique index.

Let’s try;

add / update an SQL record with id 1 and see our error:

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

 

Now let’s add our primary key and try again.

 

Now we have a primary key. Let’s try again and see the result.

 

We have seen that the value field of the record with the id 1 is updated to “a2’. Now, with the same command, let’s add a record of id 3, and let’s see the new record.

Şahap Aşçı
Author: Şahap Aşçı

Leave a Reply

Your email address will not be published. Required fields are marked *