I can't seem to find a way to achieve this transactionally (or not)
What I need to achieve is non-standard, hence my difficulty finding a solution.
I need to code a data migration tool to "swap" old records with new records in a table, but I have the following requirements / constraints:
- Don't lose the old records
- Make all references to old records point to new records (that's not just db foreign keys, it's references out of my control in external services, caches, emails, historic data, bookmarks, you name it)
- The migration code needs to be schema agnostic, i.e. shall not need to be updated if a new column is added to the table and independent of which other tables reference it.
- I can't lock the table for more than the acceptable amount of time it takes to update two records.
So my ideal solution is, well, brute swap their primary keys...
Question is how can I swap the primary keys between two records in postgresql. I am having difficulty finding an approach that does not fail with duplicate key exception, i.e. an approach that runs the "validation" for the update transactionally.
I have tried
SET id = (CASE id WHEN 1 THEN 2 WHEN 2 THEN 1 ELSE id END)
SET id = CASE id WHEN 1 THEN 2 WHEN 2 THEN 1 END
WHERE id IN (1, 2);
Both failing on duplicate key constraint
I am using PostgreSQL 11.6