Postgres UPSERT: Uplifting Database Updates and Inserts
Tags: postgres, database
Published: 2023-08-06 19:24:31

## Table of Contents:

- [Introduction](#introduction)
- [UPSERT in PostgreSQL](#upsert-in-postgresql)
- [Using UPSERT in PostgreSQL](#using-upsert-in-postgresql)
  - [Action Options](#action-options)
- [Method 1: INSERT ... ON CONFLICT](#method-1-insert--on-conflict)
- [Method 2: MERGE (UPSERT)](#method-2-merge-upsert)
- [Choosing the Right Method](#choosing-the-right-method)
- [Conclusion](#conclusion)
- [References](#references)

## Introduction

In the world of databases, updating or inserting data can be a recurring, and complex task. This is especially apparent when dealing with conflicting data or unique constraints. However, Postgres provides a powerful feature called **UPSERT**.

## UPSERT in PostgreSQL

The term **upsert** is actually the combination of the two words **up**date and in**sert**. When a new row is _inserted _into a table, PostgreSQL will perform an _update_ to the row, provided that it has found an entry for it. If not, then the new row is inserted.

This greatly eases the process of constructing queries, enabling the user to perform updates and inserts using a just one operation. 

## Using UPSERT in PostgreSQL

To use the **upsert** feature in PostgreSQL, you can use the `INSERT ON CONFLICT` statement as follows:

```sql
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (constraint_column)
DO action
```

The `ON CONFLICT target action` clause was added to the `INSERT` statement to support the upsert feature. 
(Note #1: This is not unique to PostgreSQL. Other RDBMS support it, though the syntax might be different.  For example, MySQL supports upserts using the INSERT ... ON DUPLICATE KEY UPDATE statement. Similarly, SQL Server supports upserts using the MERGE statement)
(Note #2: that the `ON CONFLICT` clause has been introduced from PostgreSQL 9.5)

In an **upsert **statement in PostgreSQL, the `DO` clause specifies the action to take when a conflict occurs during the `INSERT` operation. There are two actions that can be specified in the `DO` clause of an upsert statement in PostgreSQL:

- `DO NOTHING`: This action specifies that no changes should be made to the table if a conflict occurs. In other words, if a row already exists with the same values as the row being inserted, the `INSERT` operation will be skipped and no changes will be made to the table.
- `DO UPDATE`: This action specifies that the existing row should be updated if a conflict occurs. You can use the `SET` clause to specify which columns should be updated and what their new values should be. The `WHERE` clause can also be used to specify additional conditions for the update.


Another way of performing upserts in Postgres is by using `MERGE` (also known as `UPSERT` in some other databases). 
(Note #3: that the `MERGE` statement has been introduced from PostgreSQL 15)

## Method 1: INSERT ... ON CONFLICT

The `INSERT ... ON CONFLICT` statement allows you to specify a conflict resolution action when a unique constraint violation occurs during an insert operation. Here's the basic syntax:

```sql
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (constraint_column)
DO UPDATE SET column1 = value1, column2 = value2, ...
```

We'll use a simple example to illustrate this. Let's say we have a `users` table with columns `id`, `name`, and `email`, where `id` is the primary key. We want to insert a new user or update the name and email if the user already exists.

```sql
-- Create the users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- Insert or update a user
INSERT INTO users (id, name, email)
VALUES (1, 'John Doe', 'john@upsertdemo.com')
ON CONFLICT (id)
DO UPDATE SET name = EXCLUDED.name, email = EXCLUDED.email;
```

In the above example, if a user with `id` 1 already exists, the `name` and `email` will be updated with the new values. Otherwise, a new row will be inserted.

But wait. Where did this `EXCLUDED` come from?

In an upsert statement in PostgreSQL, EXCLUDED is a special table that is used to reference the values that were proposed for insertion in the INSERT statement. 

The `EXCLUDED` table has the same columns as the table being inserted into, and its values are the values that would have been inserted if the INSERT statement had not encountered a conflict. 

The `EXCLUDED` table is not a physical table that is stored on disk but rather a virtual table that is created and used during the execution of an `INSERT ... ON CONFLICT` statement in PostgreSQL and exists only in memory during the execution of the `INSERT ... ON CONFLICT` statement.

## Method 2: MERGE (UPSERT)


The `MERGE` statement allows you to perform both update and insert operations based on a specified condition.

Here's the basic syntax of the `MERGE` statement:

```sql
MERGE INTO target_table AS target
USING source_table AS source
ON (condition)
WHEN MATCHED THEN
    UPDATE SET column1 = value1, column2 = value2, ...
WHEN NOT MATCHED THEN
    INSERT (column1, column2, ...)
    VALUES (value1, value2, ...)
```

Let's make use of the same example as before and showcase the `MERGE` statement:

```sql
-- Create the users table
CREATE TABLE users (
   ...
);

-- Merge (UPSERT) a user
MERGE INTO users AS target
USING (VALUES (1, 'John Doe', 'john@upsertdemo.com')) AS source (id, name, email)
ON (target.id = source.id)
WHEN MATCHED THEN
    UPDATE SET name = source.name, email = source.email
WHEN NOT MATCHED THEN
    INSERT (id, name, email)
    VALUES (source.id, source.name, source.email);
```

In the above example, the `MERGE` statement first checks if a user with `id` 1 exists in the `users` table. If it does, the `name` and `email` will be updated with the new values. Otherwise, a new row will be inserted.

## Choosing the Right Method

Both `INSERT ... ON CONFLICT` and `MERGE` provide UPSERT functionality, but their usage depends on your specific requirements and the version of Postgres you are using. If you are working with Postgres versions prior to 9.5, `INSERT ... ON CONFLICT` is not available, and you should use alternative approaches like `UPDATE` followed by `INSERT` in separate statements.

If you have Postgres 9.5 or greater (but not 15), You can leverage the power of `INSERT ... ON CONFLICT`. If you are on Postgres 15 or higher, then the world is your oyster. 

The choice between them depends on factors such as performance, complexity, and personal preference. It's recommended to benchmark and compare the performance of both methods with your specific workload and dataset to make an informed decision.

## Conclusion

Updating or inserting data in a database can be a cumbersome task, especially when dealing with conflicting data. However, Postgres simplifies this process with its UPSERT functionality. In this blog post, we explored two methods to achieve UPSERT in Postgres: `INSERT ... ON CONFLICT` and `MERGE`.

We learned that `INSERT ... ON CONFLICT` allows you to specify a conflict resolution action when a unique constraint violation occurs during an insert operation. This method is suitable for Postgres versions prior to 9.5. On the other hand, `MERGE` (UPSERT) is introduced in Postgres 9.5 and provides a more concise and flexible syntax for performing both update and insert operations based on a specified condition.

Choosing the right method depends on your specific requirements and the version of Postgres you are using. It's important to consider factors such as performance, complexity, and personal preference when making the decision.

With the UPSERT functionality in Postgres, you can simplify your database operations, ensure data consistency, and streamline your application logic. Whether you choose `INSERT ... ON CONFLICT` or `MERGE`, UPSERT empowers you to efficiently handle conflicting data and keep your database up to date.

Happy UPSERTing!

## References

[UPSERT - PostgreSQL wiki.](https://wiki.postgresql.org/wiki/UPSERT.)
[Commit Fest](https://commitfest.postgresql.org/3/35/.)
[PostgreSQL: Documentation: 15: MERGE.](https://www.postgresql.org/docs/current/sql-merge.html.)
[SQL MERGE - PostgreSQL wiki](https://wiki.postgresql.org/wiki/SQL_MERGE.)
[postgresql - Difference between UPSERT and MERGE](https://dba.stackexchange.com/questions/203292/difference-between-upsert-and-merge.)
[PostgreSQL Merge | Quick Glance on PostgreSQL Merge - EDUCBA.](https://www.educba.com/postgresql-merge/.)
[How can I return EXCLUDED from WITH in PostgreSQL?.](