How to Perform the PostgreSQL Upsert in an Existing Table

Introduction

When you perform a standard UPDATE operation, the specified updates can only take place if a record exists that meets the query criteria; if no such record exists, no action is taken. What if you’d prefer to insert a new record when no existing record is found? Enter the UPSERT SQL keyword– a combination of ‘UPDATE’ and ‘INSERT’ that performs a “merge” operation. With an UPSERT. Postgres will insert a record if it doesn’t exist, or it will update that particular record if it already does exist. In this article, we’ll take a closer look at the PostgreSQL UPSERT keyword and check out some examples of its use.

Prerequisites

Before proceeding with the examples in this tutorial, you’ll need to confirm that some key prerequisites are in place:

• You must have PostgreSQL installed and working on your machine. The command systemctl status postgresql can be used in Linux distributions to verify and view the status of PostgreSQL on your device.

• You’ll also need some basic knowledge of SQL commands and database management.

Connect to psql with a database

In order to execute an UPSERT SQL statement in PostgreSQL, you’ll need to access the psql interactive shell in your terminal or command prompt window. To access psql, the first step is to switch to the postgres username:

Then, just type psql to enter the PostgreSQL command-line interface:

The next thing we’ll need to do is create a sample table with some records in it before we can try any examples using the UPSERT SQL keyword. If you already have a database you’d like to use, you can connect it to with the -d option while accessing psql. Here’s what the command would look like:

Create a database for Postgres

If you don’t already have a database created, you can easily do so using the CREATE DATABASE keywords followed by the database name:

Once you’re inside the psql interface, you can also use the \c or \connect commands, followed by a database name, to connect to another database:

Postgresql ‘CREATE TABLE’ statement

Now that we’ve accessed the psql interface and connected to our database of choice, it’s time to set up a table to use in our examples. If you don’t already have a table you’d like to use, you can use the following SQL statement to create one for your database:

This statement should return a response of CREATE TABLE if it executed successfully.

PostgreSQL UPSERT statement

As we mentioned earlier, UPSERT is a combination of two SQL commands. It allows to either to UPDATE an existing record or INSERT into the table if no matching record exists.

Let’s insert a record into the students table :

We can then use a SELECT query that will return and display the values of some_table:

The output will be:

Now, let’s use the ON CONFLICT clause in conjunction with the INSERT command and see what happens:

NOTE: The ON CONFLICT keywords used in the example above will instruct PostgreSQL not to alter any keywords in the table in the event of duplicate or conflicting data.

Notice that nothing was changed on this INSERT:

NOTE: The “conflict” mentioned in ON CONFLICT refers to the constraints that will throw an error when the same values are inserted. The DO NOTHING avoids raising such an error without modifying the pre-existing row.

You can verify that this operation was successful by executing the SELECT statement again for the record with an ID of 1516.

Postgres ‘UPSERT’ example

Next, let’s consider a situation where you do want to change the value of a column. In the following example, we’d like to change the email field from a value of ‘gerard_woodka@hotmail.com’ to a new value of ‘gerardo_woodka@gmail.com’:

Let’s run our SELECT again:

Notice the keyword EXCLUDED— this specifies the values that will be updated rather than inserted into the table.

Upsert record in multiple columns in existing table

You can upsert as many columns as you want using the same command syntax:

In our next example, we are upserting the columns firstname and lastname to the table students:

The result will look like the following:

Conclusion

When you’re performing an INSERT operation in PostgreSQL, there may be times when a duplicate record already exists in the table. If you’d prefer to update the existing row in those cases, the PostgreSQL UPSERT functionality can help you get the job done. In this tutorial, we looked at some examples of how to perform a PostgreSQL UPSERT. With these examples to guide you, you’ll be able to incorporate the same functionality into your own PostgreSQL database operations.