Posts

Showing posts from January, 2018

Swap primary keys between two records in the same table in Postgres

What if we need to swap two primary keys on the same table? What's the best and efficient way to do the same? let's check Now:- Let's assume we have a user table which has 3 columns among all the rows I have taken two sample rows which are the target rows for us. Id Name Email 101 Sabyasachi XX@gmail.com 105 Saghosh xxx@gmail.com We need to swap the primary keys (which are unique by nature) between those two records so our desired result will be something like   Id Name Email 105 Sabyasachi XX@gmail.com 101 Saghosh xxx@gmail.com Now lets buield a query using CTE(common table expression) to solve the problem:- with source_user as (   select     id   from     users   where     id = 101 ), destination_user as (   select     id   from     users   where     id