Single query UPSERTs in PostgreSQL

Let’s say you have an application that contains a request log. The log consists of 4 fields and is stored in a Postgres database. The table looks like this:

In our backend we want to use the same function to both update an existing request, or create a new one, based on if we pass in a value for ID.

Normally we’d have to write multiple queries to achieve this.
One to see if a row already exists, then based on that either UPDATE or INSERT

In this blog, we’ll explore how to achieve a so called UPSERT (update & insert) in just 1 query.

To achieve this, we will use Postgres’ ON CONFLICT statement along with a custom PSQL function. The function will manage the requests_id_seq so we don’t
a) get errors about missing data, and
b) get errors later on because the value returned from nextval('requests_id_seq') is not aligned with the table anymore.

The function used is:

This function achieves consists of a very simple IF statement that does the following:

• If an ID is not passed in, the ID is set to the next value in the ID sequence.
• If an ID is passed in and the ID is larger or equal to the highest ID currently in the table, the ID sequence is updated to be the next value after the passed in ID.

This makes sure that the ID is never null when creating the row and makes sure that updating or inserting a new ID manually doesn’t mess up the auto incrementing IDs.

Now the function has to be attached to the table, so it runs before every INSERT and UPDATE query:

Now that the setup is done, we can create the query that takes advantage of this function and allows our UPSERT.

The query consists of 2 parts:
1. The normal INSERT part of our query
2. The ON CONFLICT clause that morphs our INSERT into an UPDATE

To use this as a normal INSERT pass in (NULL, 'some_user', 'my request', 'to-do'), and swap out the NULL for an item ID to use the UPDATE.

I hope you found this useful, if you did maybe also check out the other posts on our tech-blog. Thanks for reading!