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.