UPSERT on Postgres 9.5

Postgres 9.5 RC1 was launched very recently (18th December 2015). So it's time to take some of the new features for a test drive.

The one that really caught my attention for some time is UPSERT. This basically allows you to perform an action like "create or update" in a very easy and thread safe manner.

It does so by extending the INSERT command to accept an ON CONFLICT DO clause. This clause specifies an alternative behaviour to be executed in case there would be a duplicate violation.

Lets say we have a table where we store how many times a user viewed a post. When a user views a post for the first time we create a new row with a counter set to 1. On subsequent views, we increment that counter.

We create the table with the following command.

postgres=# CREATE TABLE views (user_id INT, post_id INT, view_count INT);  
CREATE TABLE  

Next we need to add a unique constraint on user_id and post_id.

postgres=# CREATE UNIQUE INDEX ON views (user_id, post_id);  

Now comes the fun part. The user 10 just viewed the post 45 for the first time. We execute the following query.

postgres=# INSERT INTO views VALUES (10, 45, 1) ON CONFLICT (user_id, post_id) DO UPDATE SET view_count = views.view_count + 1;  

Note the ON CONFLICT (user_id, post_id) DO. We're specifying an alternative behaviour in case there's a conflict with the attributes user_id and post_id, same as we specified in our unique index.

The views table now looks like this:

postgres=# SELECT * FROM views;  
 user_id | post_id | view_count 
---------+---------+------------
      10 |      45 |          1
(1 row)

Now the user 10 is opening the post 45 again. What do we do? We just execute exactly same query. But now Postgres will detect a conflict and execute the alternative behaviour, in this case, increment the view counter.

postgres=# INSERT INTO views VALUES (10, 45, 1) ON CONFLICT (user_id, post_id) DO UPDATE SET view_count = views.view_count + 1;  
INSERT 0 1  
postgres=# SELECT * FROM views;  
 user_id | post_id | view_count 
---------+---------+------------
      10 |      45 |          2
(1 row)

And the counter just got incremented to 2.

If all you wanted was to make sure you're not inserting duplicates without sending any errors back to your user. You could also specify NOTHING as the alternative behaviour. This way Postgres will just return as the insert was successful without changing anything.

This can be very handy in many different situations. And it's specially great to have all the concurrency edge cases handled by the database, so there's less complexity for the application layer.

You can learn more about this new feature on: