PostgreSQL UPSERT for an ObjectRocket Instance

Introduction

When you insert a record into PostgreSQL, there’s an assumption that no such record already exists in the table. However, it’s possible that a duplicate record does exist, and attempting to perform the insert will result in constraint violations. What if you wanted to take action in the event that a matching record already exists, and update the existing record instead of inserting a new one? The concept of an “upsert” was designed to handle this exact scenario. In this tutorial, we’ll demonstrate how to perform a PostgreSQL UPSERT on your ObjectRocket instance using an INSERT INTO statement with the ON CONFLICT clause.

Prerequisites

You’ll need to ensure that PostgreSQL 11 is installed on your machine for this tutorial. You can use the Mission Control panel to create an instance of PostgreSQL on your ObjectRocket account.

Connect to PostgreSQL Instance

The first step in the process is to connect to PostgreSQL on our ObjectRocket instance. We can do this using the psql utility, providing the following details:

We can also use the following command in the terminal:

PostgreSQL UPSERT Statement

You can think of a Postgres UPSERT as a combination of two different SQL commands: UPDATE and INSERT. Postgres performs an update if the record already exists; otherwise, it inserts the data as a new record.

To perform an UPSERT, we use an INSERT INTO ON CONFLICT statement. We can see the basic form of this statement below:

Let’s examine this syntax in a bit more detail:

• The ON CONFLICT target action clause is what turns this INSERT statement into a Postgres UPSERT. We can use the ON CONFLICT clause to prevent errors due to duplicate records or constraint violations; however, the clause can also be used to tell Postgres to perform an update when an insert is not possible.

• The target can be any of the following: 1. WHERE clause 2. ON CONSTRAINT 3. A column name

• The action, as the name implies, specifies what action should be taken: 1. DO NOTHING: Don’t perform any action if the record already exists within the table. 2. DO UPDATE SET column = value WHERE condition: This performs an update on fields that satisfy the given criteria.

Sample Data Set

We’ll be using the following data set for the purposes of this tutorial:

Let’s look at the structure of our sample person table:

PostgreSQL UPSERT Example

In this example, we’ll change the value of the column name from ‘ivan’ to ‘james’:

After executing the above statement, we receive this response: INSERT 0 1. Let’s see if the record was updated using a SELECT statement:

Notice that we used the EXCLUDED clause in our INSERT INTO statement. This instructs Postgres to update the record with the new specified value instead of inserting it as a new record.

Conclusion

When you attempt to insert new records in PostgreSQL, there’s a chance that the same records may already exist in the table. This can lead to constraint violations and other issues. An UPSERT operation allows you to handle these scenarios in a graceful manner, updating the existing record instead of inserting a duplicate or returning an error. This tutorial explained how to perform a PostgreSQL UPSERT in ObjectRocket using the INSERT INTO statement in conjunction with a ON CONFLICT clause. If you’ve been following along with the examples in this article, you’ll be prepared to perform an UPSERT in your own Postgres environment.