In this tutorial, we will learn about the INSERT ON CONFLICT statement which is used to perform the upsert operation. If you don’t know what is the upsert operation, don’t worry, we will cover everything in this tutorial. So without further wasting time, let’s get started!
Also Read: PostgreSQL – A Complete Introduction, History, and Features
Introduction to PostgreSQL Upsert
The upsert statement is simply a combination of the update and the insert operation. That means, if you are trying to insert some record and that record already exists, the record will simply get updated with the new values, otherwise it will be inserted. As simple as that!
Many DBMSes provide the feature of upsert operation out of the box. Note that, in PostgreSQL, the upsert is not a keyword and many DBMSes provide this feature in different ways.
Now let’s see some information about the INSERT ON CONFLICT statement.
INSERT ON CONFLICT Statement
In PostgreSQL, the INSERT … ON CONFLICT statement is used to do the upsert operation, which is also known as the merge operation.
Let’s see the syntax of the INSERT ON CONFLICT statement:
INSERT INTO tableName(columnList)
VALUES(values)
ON CONFLICT
target
action;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)where target can be any of the following:
- columnName: Name of the column against which you want to check the conflict
- ON CONSTRAINT constraintName: Name of the unique constraint
- WHERE clause: Any WHERE condition
and action can be any of the following:
- DO NOTHING: If you don’t want to perform anything on conflict occurrence, use this
- DO UPDATE …: The UPDATE statement to set the new values if a conflict occurs
Now let’s see some examples so that you will quickly understand how the target and action are used.
PostgreSQL UPSERT Examples
Let’s now create a table and insert some data into it.
DROP TABLE account;
CREATE TABLE account (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
age INTEGER
);
INSERT INTO account(name, email, age)
VALUES
('John Doe', 'johndoe@example.com', 25),
('Jane Smith', 'janesmith@example.com', 30),
('Alice Johnson', 'alicejohnson@example.com', NULL),
('Michael Brown', 'michaelbrown@example.com', 40),
('Emily Davis', 'emilydavis@example.com', 35),
('David Wilson', 'davidwilson@example.com', 28);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Output:
Perfect! The table has been created successfully with the correct data.
Check Out: PostgreSQL CREATE TABLE – How to Create a Table in PostgreSQL?
Now let’s take a scenario. We have records in our table already and we also have the same records with updated age values. Now, we will load all the records into the table using the ON CONFLICT statement, so that old values will get replaced instead of re-creating new records.
Upsert Using DO UPDATE
Note that, the email column is unique in our table. Therefore, we will use the email column to check the conflicts.
Example:
INSERT INTO account(name, email, age)
VALUES
('John Doe', 'johndoe@example.com', 27),
('Jane Smith', 'janesmith@example.com', 32),
('Alice Johnson', 'alicejohnson@example.com', 33),
('Michael Brown', 'michaelbrown@example.com', 42),
('Emily Davis', 'emilydavis@example.com', 37),
('David Wilson', 'davidwilson@example.com', 30),
('Tom Cruise','tomcruise@gmail.com',34)
ON CONFLICT(email)
DO UPDATE
SET age = EXCLUDED.age;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, the EXCLUDED keyword simply picks up the new values and set them. Read this thread to know more about the EXCLUDED keyword.
Now let’s see the output.
Output:
As you can see, the output of the above query shows 7 rows are inserted(out of which, 6 are updated). The SELECT query displays all the updated values along with the newly inserted record.
Upsert Using DO NOTHING
If you want to do nothing when the new values are different than the existing values but still want to insert the records which are new, simply use the DO NOTHING clause.
Example:
INSERT INTO account(name, email, age)
VALUES
('John Doe', 'johndoe@example.com', 30),
('Tom Cruise','tomcruise@gmail.com',37),
('Tom Holand','tomholand@hotmail.com',20)
ON CONFLICT(email)
DO NOTHING;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, the query will simply skip to the next row if the conflict occurs. When the new row doesn’t have any conflicts, it will simply insert that in the table. Let’s see the output of the above query.
Output:
As you can see, the query returned “inserted 0 1”, which means only 1 row has been inserted, which means conflicts were ignored. In the SELECT query output, you can see all previous values kept as they were.
If you want to check the conflicts against more than one column, you can create a constraint on the table and use that constraint as per the syntax given earlier.
Conclusion
In this tutorial, we have learned to perform the upsert operation using the INSERT … ON CONFLICT statement. I hope you have learned something new in this tutorial and understood everything without any problems. If you think the tutorial was helpful, don’t forget to share it with your friends!