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:
1 2 3 4 5 | Server [localhost]: ingress.w98sujpz.launchpad.objectrocket.cloud DATABASE [PostgreSQL]: <your_database_name> Port [5432]: 4149 Username [PostgreSQL]: pguser Password FOR USER orkb: <your_password> |
We can also use the following command in the terminal:
1 | psql -h ingress.w98sujpz.launchpad.objectrocket.cloud -U pguser -d PostgreSQL -p 4149 |
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:
1 2 | INSERT INTO tableName(list_of_column) VALUES(list_of_values) ON CONFLICT target action; |
Let’s examine this syntax in a bit more detail:
The
ON CONFLICT target action
clause is what turns thisINSERT
statement into a Postgres UPSERT. We can use theON 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 nameThe
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:
1 2 3 4 5 6 | id | name | age ----+-------+----- 1 | ivan | 9 2 | john | 10 3 | haley | 10 (3 rows) |
Let’s look at the structure of our sample person
table:
1 2 3 4 5 6 7 8 9 | Table "public.person" Column | Type | Collation | Nullable | Default --------+------------------------+-----------+----------+--------- id | integer | | not null | name | character varying(200) | | | age | integer | | | Indexes: "person_pkey" PRIMARY KEY, btree (id) "unique_name" UNIQUE CONSTRAINT, btree (name |
PostgreSQL UPSERT Example
In this example, we’ll change the value of the column name
from ‘ivan’ to ‘james’:
1 2 3 4 5 6 7 8 | INSERT INTO person (id,name,age) VALUES ( 1, 'james', 9 ) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name; |
After executing the above statement, we receive this response: INSERT 0 1
. Let’s see if the record was updated using a SELECT
statement:
1 2 3 4 5 6 7 | persondb=> SELECT * FROM person; id | name | age ----+-------+----- 2 | john | 10 3 | haley | 10 1 | james | 9 (3 ROWS) |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started