How to Perform the PostgreSQL Upsert in an Existing Table

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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:

1
sudo su - postgres

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

1
psql

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:

1
psql -U postgres -d some_db

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:

1
CREATE DATABASE some_db;

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:

1
\c some_db

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:

1
2
3
4
5
6
7
8
CREATE TABLE students(
    id INT NOT NULL PRIMARY KEY,
    firstname VARCHAR(20) NOT NULL,
    lastname VARCHAR(50) NOT NULL,
    gender VARCHAR(2),
    d_o_b DATE,
    email TEXT
);

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 :

1
2
INSERT INTO students(id, firstname, lastname, gender, d_o_b, email)
VALUES (1516, 'Gerard', 'Woodka', 'M', 'January 27 1995', 'gerard_woodka@hotmail.com');

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

1
SELECT * FROM students WHERE id = 1516;

The output will be:

1
2
3
4
  id  | firstname | lastname | gender |   d_o_b    |           email
------+-----------+----------+--------+------------+---------------------------
 1516 | Gerard    | Woodka   | M      | 1995-01-27 | gerard_woodka@hotmail.com
(1 row)

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

1
2
3
4
INSERT INTO students(id, firstname, lastname, gender, d_o_b, email)
VALUES
(1516, 'Gerard', 'Woodka', 'M', 'January 27 1995', 'gerard_woodka@hotmail.com')
ON CONFLICT (id) DO NOTHING;

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:

1
INSERT 0 0

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

1
2
3
4
INSERT INTO students(id, firstname, lastname, gender, d_o_b, email)
VALUES
(1516, 'Gerard', 'Woodka', 'M', 'January 27 1995', 'gerardo_woodka@gmail.com')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;

Let’s run our SELECT again:

1
2
3
4
  id  | firstname | lastname | gender |   d_o_b    |          email
------+-----------+----------+--------+------------+--------------------------
 1516 | Gerard    | Woodka   | M      | 1995-01-27 | gerardo_woodka@gmail.com
(1 row)

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:

1
2
3
INSERT INTO students(id, firstname, lastname, gender, d_o_b, email)
VALUES (1516, 'Gerardo', 'Wood', 'M', 'January 27 1995', 'gerardo_woodka@gmail.com')
ON CONFLICT (id) DO UPDATE SET firstname = EXCLUDED.firstname, lastname = EXCLUDED.lastname;

The result will look like the following:

1
2
3
4
  id  | firstname | lastname | gender |   d_o_b    |          email
------+-----------+----------+--------+------------+--------------------------
 1516 | Gerardo   | Wood     | M      | 1995-01-27 | gerardo_woodka@gmail.com
(1 ROW)

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.