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
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
Now that the setup is done, we can create the query that takes advantage of this function and allows our
The query consists of 2 parts:
1. The normal
INSERT part of our query
ON CONFLICT clause that morphs our
INSERT into an
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
I hope you found this useful, if you did maybe also check out the other posts on our tech-blog. Thanks for reading!