POSTGRESQL
UPSERT

PostgreSQL Upsert: INSERT ON CONFLICT Guide

intro

Subtitle: Let’s explore the upsert operation in PostgreSQL to programmatically insert new data or update existing data with a single query.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE

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:

  • Inserts a new row in a table if this record does not already exist based on the conflict conditions, or
  • Updates the existing record with the new values

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:

  • Simplified query logic
  • Reduced database round-trips
  • Better data integrity as the INSERT or UPDATE operation is performed atomically.

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:

Copy
        
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:

  • The list of columns that are primary keys, are in a UNIQUE index, or have a UNIQUE constraint. These can be followed by an optional WHERE clause with a predicate condition that returns TRUE or FALSE.
  • ON CONSTRAINT <constraint_name>, where <constraint_name> is the name of a UNIQUE constraint in the table.

When omitted, PostgreSQL automatically tries to apply all UNIQUE constraints and indexes available.

<conflict_action> accepts two values:

  1. DO NOTHING: When a conflict triggers, it simply skips the insert operation. In other words, it does nothing.
  2. DO UPDATE <update_statement>: When a conflict triggers, it performs <update_statement>. A <conflict_condition> is required when using DO UPDATE.

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:

Copy
        
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:

Copy
        
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:

The DDL definition of the "products" table provided by DbVisualizer
The DDL definition of the "products" table provided by DbVisualizer

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:

Copy
        
1 ALTER TABLE products ADD CONSTRAINT products_unique_ctr1 UNIQUE(name, model);

You can now perform an upsert statement as follows:

Copy
        
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:

Note that the query was executed correctly
Note that the query was executed correctly

All the following times, it will not perform any operation:

Note that no rows were added to the table
Note that no rows were added to the table

Similarly, you can achieve the same result by referencing the constraint name with ON CONSTRAINT:

Copy
        
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;
Again, no rows were added
Again, no rows were added

Scenario 2: Using a UNIQUE Index

Define a UNIQUE index on name and model in products with:

Copy
        
1 CREATE UNIQUE INDEX products_unique_idx1 ON products(name, model);

You cannot execute a PostgreSQL upsert statement as follows:

Copy
        
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;
Note that the query was performed successfully
Note that the query was performed successfully

The <Arex GLX, Pro> product will now contain a new tagline:

The tagline was updated as expected
The tagline was updated as expected

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:

Copy
        
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:

Copy
        
1 PRIMARY KEY (order_id, customer_id)

Then, a valid upsert statement might be:

Copy
        
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.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

Adding Dates in SQL: A Complete Tutorial

author Antonello Zanini tags DATE DATETIME MySQL POSTGRESQL SQL SERVER 7 min 2024-04-15
title

Glossary of the SQL Commands You Need to Know

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 12 min 2024-04-11
title

SQL Add to Date Operations: A Complete Guide

author Antonello Zanini tags DATE DATETIME MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-04-01
title

SQL CAST Function: Everything You Need to Know

author Antonello Zanini tags CAST MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-03-28
title

Postgres TEXT vs VARCHAR: Comparing String Data Types

author Antonello Zanini tags POSTGRESQL TEXT VARCHAR 6 min 2024-03-14
title

Schemas in PostgreSQL

author Leslie S. Gyamfi tags POSTGRESQL SCHEMA 6 min 2024-03-11
title

5 Ways to Split a String in PostgreSQL

author Antonello Zanini tags POSTGRESQL Split 6 min 2024-02-29
title

PostgreSQL CASE: A Comprehensive Guide

author Leslie S. Gyamfi tags CASE POSTGRESQL 7 min 2024-02-22
title

Discover All PostgreSQL Data Types

author Antonello Zanini tags DATA TYPES POSTGRESQL 12 min 2024-02-05
title

A Complete Guide to pg_dump With Examples, Tips, and Tricks

author Antonello Zanini tags pg_dump POSTGRESQL 8 min 2024-01-25

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗