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
UPDATE table SET id = (CASE id WHEN 1 THEN 2 WHEN 2 THEN 1 ELSE id END)
UPDATE table 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
Solution for bulk updates for numeric primary keys, thanks to input from the nice people below:
-- disable foreign key constraint validation BEGIN; SET session_replication_role='replica'; -- update the pairs of ids to their negative counterparts WITH query AS ('query to get pairs of ids to swap') UPDATE table SET id = -id WHERE id in (query.id1, query.id2); -- update the pairs of negated ids to their positive counterparts swapped WITH query AS ('query to get pairs of ids to swap') UPDATE table SET id = CASE id WHEN query.id1 THEN -query.id2 WHEN query.id2 THEN -query.id1 END WHERE id in (query.id1, query.id2); -- enable foreign key constraint validation SET session_replication_role='original'; COMMIT;