PostgreSQL - UPSERT

---

---

In PostgreSQL, UPSERT involves two operations − "UPDATE" and "INSERT". This allows you to insert a new row into a table while handling conflicts. The conflict occur when you try to insert a new row into a table, but a row with the same unique or primary key already exists.

For better understanding, imagine you have a table with a column showing uniqueness, like an email address or ID. If you want to insert a new row with an email address or ID that is already present in the table, PostgreSQL detects the conflicts because they occur in unique constraints.

So this way, UPSERT comes into part of two operations. Instead of throwing an error, PostgreSQL follows the two things −

• UPDATE the existing row − If there is a conflict.
• INSERT a new row − If there is no conflict.

Syntax

Following is the syntax of UPSERT in PostgreSQL −

INSERT INTO table_name (col1, col2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_column)
DO UPDATE SET col1 = EXCLUDED.col1, col2 = EXCLUDED.col2;

Or,

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_column)
DO NOTHING;

Here,

• table_name : The name of the table.
• col1, col2, ... : The column to be inserted.
• value1, value2, ... : The values are corresponding to the column.
• conflict_column : The column that cause conflict like unique key or primary key.
• EXCLUDED.column_name : This is the way to access the "new" data that caused the conflict.
• DO UPDATE SET : If the conflict occurs then update the existing row with new values.
• DO NOTHING : If a conflict occurs the ignore the insert operation.

How UPSERT works?

Here is the stepwise explanation of how UPSERT works in PostgreSQL using custom query −

1. Create a table

First create the table with a unique key or primary key.

CREATE TABLE person (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    salary NUMERIC
);

Here,

id is the primary key, so it must be unique.

If you try to insert a row with an existing id, a conflict will occur.

2. Insert initial data

To insert the initial data, follow the below query −

INSERT INTO person (id, name, salary)
VALUES (1, 'Alex', 50000);

3. Insert the conflicting row

Now, try to insert a new row with the same id but different values.

INSERT INTO person (id, name, salary)
VALUES (1, 'John', 60000);

The above query generate the following result −

Therefore, this will fail because id=1 already exists and cause a conflict.

4. UPSERT to handle the conflict

To handle the conflict, use the INSERT ... ON CONFLICT statement. This will either insert the new row or update the existing one.

INSERT INTO person (id, name, salary)
VALUES (1, 'John', 60000)
ON CONFLICT (id) 
DO UPDATE SET 
    name = EXCLUDED.name, 
    salary = EXCLUDED.salary;

The above query generate the following result −

5. Print the table

The SELECT statement help us to print the result.

SELECT * FROM person;

6. Insert a new row without conflict

If you insert a row with a new id, it will be added without any conflict.

INSERT INTO person (id, name, salary)
VALUES (2, 'John', 70000)
ON CONFLICT (id) 
DO UPDATE SET 
    name = EXCLUDED.name, 
    salary = EXCLUDED.salary;

The above query print the following rsult −

INSERT 0 1
Query returned successfully in 67 msec.

Now, check the tabular result which shows the resultant of two rows using SELECT statement.

The main point of UPSERT is not to change the id values. Instead, the purpose of UPSERT is to handle conflicts that occur when you try to insert a row with a value that already exists in a unique column (like a primary key or unique constraint).