PostgreSQL 9.5: Insert IF not Exists, Update IF Exists (Insert ON CONFLICT option)

After a long time of waiting, PostgreSQL 9.5 introduced INSERT ON CONFLICT [DO UPDATE] [DO NOTHING].

This option basically helps to perform DML actions like, Insert IF not Exists, Update IF Exists.

Previously, we have to use upsert or merge statement to do this kind of operation. I have also published an article on it.

PostgreSQL: Insert – Update or Upsert – Merge using writable CTE

This newly option has two varieties:

  • INSERT ON CONFLICT DO UPDATE: If record matched, it is updated with the new data value.
  • INSERT ON CONFLICT DO NOTHING: If record matched, it skips the record or error.

Below is a full demonstration of this:

Create a table with sample data:

Insert one more row with option INSERT ON CONFLICT DO UPDATE:

Using this option, if conflict occurs then it will update the mentioned data.
Here, I have use “Excluded” table which is special table and contains the row-to-be-inserted.

Try to update last inserted EmpName, using option INSERT ON CONFLICT DO UPDATE:

Try to insert duplicate EmpID record, using option INSERT ON DO NOTHING:
Using this option, if conflict occurs then it will not take any action or any error.

Check the result:


Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com