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