Site icon Database Tutorials

PostgreSQL Upsert

In today’s article, we will be cover about the Upsert feature that came into our lives with PostgreSQL 9.5.

Upsert makes an insert if there is no record in the table, and if there is, it uses the specified column to update it.

While doing this, your table must have at least one unique value.

General use:

The part that starts with insert above is the insert clause we use to add data.

ON CONFLICT DO NOTHING

If it is the same as the record in the table to be added, it means making a transaction.

ON CONFLICT (column_name) DO UPDATE

The data to be added exists in the table, but it is used if it is desired to be changed.

Let’s reinforce it with practice.

First, let’s create our aupsert table in our table.

Let’s add our data to our table.

After adding our data, let’s query our table.

Let’s see how it will behave in the ON CONFLICT DO NOTHING parameter by adding a record with the same name to our table.

Above, we see that it gives output as “INSERT 0 0”. This means that data cannot be added to our table.

We found that our data was not appended. Let’s try to add the same data using the ON CONFLICT (column_name) DO UPDATE command.

We specify the unique column with the ON CONFLICT(k1) parameter.

In the DO UPDATE SET command, we write the columns to be changed.

In this example, our output has changed to 0 1, let’s check our data by pulling a select from the table.

As seen above, we see that our ERDEM record in column k3 has changed.

You can use the example below to use upsert between different tables.

First, let’s create our table and add our data.

After adding our data, let’s transfer the data in the aupsert2 table to the aupsert table.

First, let’s view our data in our table.

Let’s list the data in the aupsert2 table.

Let’s add the data that is different between our first table and our second table to our first table with the help of the following command.

As it can be seen above, since there are no 4th and 5th records, it has been added to our aupsert table and in the 3rd record, “ER” in column k3 has been updated as “ERDEM”.

With the example above, the insert and update process were carried out together.

Exit mobile version