How to swap primary key between records in postgres
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
.
The only way that I know this is possible is with a deferred constraint.
You will need to drop the primary key,
alter table x drop constraint x_pkey;
and add it again as deferrable:
alter table x add primary key (id) deferrable initially immediate;
Before performing the update you can defer the constraint:
set constraints x_pkey deferred;
and then perform the update and commit
.
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=469e5168c84bdbb8361426d4458bcf88
Of course, if you have other tables referring to this table you will need to drop and recreate the foreign keys too.