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

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz