How to swap primary key between records in postgres

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

You could use a deferred constraint. For that you need to drop and re-create the primary key:

CREATE UNIQUE INDEX mytable_primkey ON mytable (id);
ALTER TABLE mytable DROP CONSTRAINT mytable_pkey;
ALTER TABLE mytable ADD PRIMARY KEY USING INDEX mytable_primkey
   DEFERRABLE INITIALLY DEFERRED;

The update itself could then be done like that:

UPDATE mytable SET id = 3 - id WHERE id IN (1, 2);

Here 1 and 2 are used as examples, you can do that with any numbers.

If you cannot afford the down time required for adding a deferred primary key, you could do it with one more update like this;

BEGIN;
UPDATE mytable SET id = 0 WHERE id = 1;
UPDATE mytable SET id = 1 WHERE id = 2;
UPDATE mytable SET id = 2 WHERE id = 0;
COMMIT;

Here 0 is an arbitrary value that is not used as a value for id.

StackExchange Database Administrators Q#262071, answer score: 8