intro
Subtitle: Let’s explore the upsert operation in PostgreSQL to programmatically insert new data or update existing data with a single query.
A common scenario when dealing with databases is to insert a record only if it is not present. Otherwise, to avoid duplicates, you want to update the existing record with new values. Normally, this operation would require application-level logic. However, it is so common that most databases offer it through the “upsert” statement. Specifically, the PostgreSQL upsert operation is atomic and performs an INSERT, or an UPDATE when a conflict occurs.
In this guide, you will dig into the INSERT ON CONFLICT statement, the tool offered by PostgreSQL to perform upserts. You will understand in-depth what an upsert is, how to perform it in PostgreSQL, and see some real-world examples.
What Does Upsert Mean?
The term "upsert" comes from the union of the words “update” and “insert.” In the realm of databases, this operation ensures that a specific record is either added to the database if it does not exist or modified if it already exists. The database determines whether or not the record exists based on some conflict conditions specified in the upsert query.
Thus, when performing an upsert the DBMS:
Note that these two operations are performed atomically within the same SQL statement. This means that running an SQL upsert statement is different from performing an INSERT or an UPDATE conditionally.
The idea behind this operation is to maintain data integrity by ensuring that duplicate records are not inserted into a table. In detail, the advantages of using an upsert compared to separate insert and update statements are:
PostgreSQL Upsert: Is It Possible?
Short answer: Yes, performing an upsert in PostgreSQL is possible!
PostgreSQL supports the upsert operation through the INSERT ... ON CONFLICT statement. This feature has been part of the popular relational database since version 9.5. Specifically, ON CONFLICT is an optional clause that triggers a particular action when the INSERT statement raises a UNIQUE constraint violation. That action can be an update operation or a simple skip.
Let’s now jump into the INSERT ... ON CONFLICT syntax to understand how the PostgreSQL upsert operation works.
How To Perform an Upsert in Postgres with INSERT ON CONFLICT
In PostgreSQL, you can perform an upsert with an INSERT ... ON CONFLICT statement, which has the following syntax:
1
INSERT INTO <table_name>(<column_list>)
2
VALUES(<value_list>)
3
ON CONFLICT <conflict_condition> <conflict_action>;
The first two lines of the query correspond to a usual INSERT statement. What changes is that the ON CONFLICT clause accepts an optional <conflict_condition> and a required <conflict_action>.
In particular, <conflict_condition> can be:
When omitted, PostgreSQL automatically tries to apply all UNIQUE constraints and indexes available.
<conflict_action> accepts two values:
Keep in mind that if you apply an INSERT ... ON CONFLICT statement on columns that do not have the right constraints, you will get the following error:
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
Understanding Upserts
To better understand how the upsert works in PostgreSQL, let’s take a look at the example below:
1
INSERT INTO users(name, surname, email)
2
VALUES('John', 'Smith', 'john.smith@example.com')
3
ON CONFLICT(email)
4
DO NOTHING;
Assume that email has a UNIQUE constraint, this query will insert the record <'John', 'Smith', 'john.smith@example.com'> only if there is already a user with the email john.smith@example.com.
If you instead want to update the record values on a conflict, you can write:
1
INSERT INTO users(name, surname, email)
2
VALUES('John', 'Smith', 'john.smith@example.com')
3
ON CONFLICT(email)
4
DO UPDATE SET name = EXCLUDED.name, surname = EXCLUDED.surname;
This time, the query will insert the record <'John', 'Smith', 'john.smith@example.com'> or update the name and surname column of the existing john.smith@example.com user.
Note that EXCLUDED is a special keyword that allows you to access the values of the record excluded from insertion.
PostgreSQL Upsert Examples
Here, you will dig into the different conflict resolution options available with the Postgres upsert operation.
Assume you have a products table defined as shown below:
Note that the table has the id, name, price, description, tagline, and model columns.
Time to see different conflict resolution scenarios! To better understand the result of this operation, executing the upsert statements in visual database tool is recommended. Here, we will rely on DbVisualizer, a powerful and complete PostgreSQL database client.
Scenario 1: Using a UNIQUE Constraint
Add a UNIQUE constraint on name and model in products with:
1
ALTER TABLE products ADD CONSTRAINT products_unique_ctr1 UNIQUE(name, model);
You can now perform an upsert statement as follows:
1
INSERT INTO products(name, model, tagline)
2
VALUES ('Arex GLX', 'Pro', 'The best smartphone on the market')
3
ON CONFLICT (name, model)
4
DO NOTHING;
The first time you launch it, it will add a new record to the products table:
All the following times, it will not perform any operation:
Similarly, you can achieve the same result by referencing the constraint name with ON CONSTRAINT:
1
INSERT INTO products(name, model, tagline)
2
VALUES ('Arex GLX', 'Pro', 'The best smartphone on the market')
3
ON CONFLICT (name, model)
4
DO NOTHING;
Scenario 2: Using a UNIQUE Index
Define a UNIQUE index on name and model in products with:
1
CREATE UNIQUE INDEX products_unique_idx1 ON products(name, model);
You cannot execute a PostgreSQL upsert statement as follows:
1
INSERT INTO products(name, model, tagline)
2
VALUES ('Arex GLX', 'Pro', 'The best smartphone on the market')
3
ON CONFLICT (name, model)
4
DO UPDATE tagline = EXCLUDED.tagline;
The <Arex GLX, Pro> product will now contain a new tagline:
This time, you cannot perform an upsert based on the UNIQUE index name.
Et voilà! You just became a Postgres upsert master!
Conclusion
In this article, you saw what the PostgreSQL upsert is, how it works, and how to use it in real-world scenarios. Thanks to what you learned here, you are now equipped with the expertise required to deal with conditional inserts in Postgres.
Upsert is a powerful feature that requires an equally powerful database client. This is where DbVisualizer comes into play! In addition to supporting all PostgreSQL features, this tool supports dozens of DBMSs and also offers advanced query optimization and drag-and-drop query construction. Take your database processes to the next level. Download DbVisualizer for free now!
FAQ
What is the performance of an upsert operation in PostgreSQL?
The performance of an upsert operation in PostgreSQL can be comparable to or even faster than performing a separate insert and update query. The reason is that the upsert combines both operations into a single query, reducing the number of database round-trips. This is especially true when dealing with large datasets.
Is it possible to perform a PostgreSQL bulk upsert operation?
Yes, PostgreSQL bulk upsert is possible. For example, you can achieve it as below:
1
INSERT INTO table_to (SELECT * FROM table_from)
2
ON CONFLICT ON CONSTRAINT table_to_unique_ctr
3
DO UPDATE SET column1 = EXCLUDED.column1, column2 = EXCLUDED.column2;
How to upsert data in tables that have composite primary keys?
To upsert data in tables that have composite primary keys in PostgreSQL, you just need to specify all the key columns in the ON CONFLICT clause. For example, assume that the orders table has the following primary key:
1
PRIMARY KEY (order_id, customer_id)
Then, a valid upsert statement might be:
1
INSERT INTO orders(order_id, customer_id, order_total)
2
VALUES (14, 48, 150.00)
3
ON CONFLICT (order_id, customer_id) DO UPDATE
4
SET order_total = excluded.order_total;
Is it possible to specify multiple conflict resolution strategies in a single PostgreSQL upsert statement?
No, it is not possible. In PostgreSQL's INSERT ... ON CONFLICT statement, you can only specify a single conflict resolution strategy for each upsert statement.
Do MySQL, SQL Server, and Oracle support upserts?
Yes, MySQL, SQL Server, and Oracle all have the upsert feature. MySQL exposes the INSERT ... ON DUPLICATE KEY UPDATE syntax, while SQL Server and Oracle offer the MERGE statement.
