PostgreSQL Upsert

Faruk Erdem

1 year ago

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:

INSERT INTO Table_name (column1 ,column2,….) VALUES (VALUE1,VALUE2,….)
ON CONFLICT DO NOTHING
or 
ON CONFLICT (column_name) DO UPDATE

INSERT INTO Table_name (column1 ,column2,….) VALUES (VALUE1,VALUE2,….)

ON CONFLICT DO NOTHING

ON CONFLICT (column_name) DO UPDATE

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.

CREATE TABLE  aupsert (k1 int unique ,k2 char(10),k3 char(10))

1	CREATE TABLE aupsert (k1 int unique ,k2 char(10),k3 char(10))

Let’s add our data to our table.

INSERT INTO aupsert VALUES (1,'Faruk','ERDEM');
INSERT INTO aupsert VALUES (2,'Ufuk','ERDEM');
INSERT INTO aupsert VALUES (3,'Utku','ERDEM')

INSERT INTO aupsert VALUES (1,'Faruk','ERDEM');

INSERT INTO aupsert VALUES (2,'Ufuk','ERDEM');

INSERT INTO aupsert VALUES (3,'Utku','ERDEM')

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.

INSERT INTO aupsert VALUES (3,'Utku','ER')
ON CONFLICT (k1)
DO    UPDATE SET k2 = EXCLUDED.k2 , k3 = excluded.k3 ;

INSERT INTO aupsert VALUES (3,'Utku','ER')

ON CONFLICT (k1)

DO UPDATE SET k2 = EXCLUDED.k2 , k3 = excluded.k3 ;

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.

CREATE TABLE aupsert2 (k1 int unique ,k2 char(10),k3 char(10))
INSERT INTO aupsert2 VALUES (3,'Utku','ERDEM');
INSERT INTO aupsert2 VALUES (4,'Ahmet','ASLAN');
INSERT INTO aupsert2 VALUES (5,'Mehmet','KIR');

CREATE TABLE aupsert2 (k1 int unique ,k2 char(10),k3 char(10))

INSERT INTO aupsert2 VALUES (3,'Utku','ERDEM');

INSERT INTO aupsert2 VALUES (4,'Ahmet','ASLAN');

INSERT INTO aupsert2 VALUES (5,'Mehmet','KIR');

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.

INSERT INTO aupsert(k1,k2,k3)
SELECT k1,k2,k3
FROM aupsert2
ON CONFLICT (k1)
DO
   UPDATE SET k2 = EXCLUDED.k2 , k3 = excluded.k3 ;

INSERT INTO aupsert(k1,k2,k3)

SELECT k1,k2,k3

FROM aupsert2

ON CONFLICT (k1)

UPDATE SET k2 = EXCLUDED.k2 , k3 = excluded.k3 ;

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.