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

In this post, I demonstrate to write merge statement using using CTE of PostgreSQL.
PostgreSQL 9.1 has a new feature to write CTE and using this CTE you can also manage INSERT / UPDATE using one expression.

The merge statement is always a discussion point for Database Developers of MySQL/PostgreSQL.
Below is a full demonstration to insert a record if not exist and update a record if exist.

Let’s first create one sample table:

Now create one Stored Function:

As you can see in the above function that CTE is created with two input parameters and manage INSERT/UPDATE using one expression.
Now call this function with sample data:

You can find two records have same rno column and “Neevan” will update by “Roy”.
Check this result:

PostgreSQL Upsert Merge

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, 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

Leave a Reply

Be the First to Comment!

Notify of