PostgreSQL: UPSERT

Itsuki

Published in

CodeX

3 min readAug 2, 2024

*******

DISCLAIMER!!! There is no such statement called UPSERT!

*******

What is UPSERT?

It is a combination of INSERT and UPDATE. This idea allows us to update an existing row or insert a new one if it doesn’t exist.

Let’s start from the very basic syntax and move from there!

Note that the features we are using in this article is only available for PostgreSQL 9.5+.

Basic Syntax

Basic is always a good place to start!

As I have said above, there is no such statement called UPSERT. It is done through INSERT...ON CONFLICT statement.

Here’s the basic syntax.

INSERT INTO table_name (column1, column2, ...)
VALUES 
    (col1_value, col2_value, ...)
ON CONFLICT (conflict_column)
DO NOTHING | DO UPDATE SET 
    column1 = col1_value, 
    column2 = col2_value, ...;

table_name: The name of the table into which we want to insert into.
(column1, column2, ...): The list of columns we want to insert.
VALUES (col1_value, col2_value, ...): The values we want to insert.
ON CONFLICT (conflict_column): The conflict target column name. This column (or columns) has to be a unique constraint or unique index that may cause a conflict.
DO NOTHING: Just like the name suggests, do nothing when a conflict occurs.
DO UPDATE: Performs an update on conflict
SET column = col1_value, column = col2_value, ...: This list of the columns to be updated and their corresponding values in case of conflict.

Simple Example

Let’s try it out with a simple example where we have a test_user table that is defined like following.

CREATE TABLE test_user(
   id INT PRIMARY KEY,
   name VARCHAR(255) NOT NULL,
   age INT not NULL
);

And insert some sample data into it!

INSERT INTO public.test_user (id, "name", age)
values
 (0, 'itsuki0', 100000), 
 (1, 'itsuki1', 100000), 
 (2, 'itsuki2', 100000), 
 (3, 'itsuki3', 100000), 
 (4, 'itsuki4', 100000), 
 (5, 'itsuki5', 100000)
RETURNING *;

Time to try out UPSERT!

In the case of a non-existing row, no conflict occurs and the row is inserted!

INSERT INTO public.test_user (id, "name", age)
values
 (6, 'itsuki6', 100001)
ON CONFLICT(id) 
DO UPDATE SET
  age = 100001;

For an existing row, the age is updated to its new value.

INSERT INTO public.test_user (id, "name", age)
values
 (0, 'itsuki0', 1000000000)
ON CONFLICT(id) 
DO UPDATE SET
  age = 1000000000;

UPSERT For Multiple Rows

If you think about the syntax we have above carefully, you will realize that it will NOT work if we try to insert multiple rows where each row has a different target value!

We cannot DO UPDATE SET multiple rows by specifying the value like we did above, ie: age = someNumber, or name = someName.

Here is where the EXCLUDE comes in handy. I think of it as a special table that keeps a reference to the values we attempted to insert!

Here is how we can use it to insert/update multiple rows all at once!

INSERT INTO public.test_user (id, "name", age)
values
 (0, 'itsuki0', 1),
 (1, 'itsuki1', 2),
 (2, 'itsuki2', 3),
 (3, 'itsuki3', 4),
 (100, 'itsuki100', 100),
 (101, 'itsuki101', 101)
ON CONFLICT(id) 
DO UPDATE SET
  age = EXCLUDED.age;

And here is what we end up with!