Swap unique indexed column values in database

Question

I have a database table and one of the fields (not the primary key) is having a unique index on it. Now I want to swap values under this column for two rows. How could this be done? Two hacks I know are:

1. Delete both rows and re-insert them.
2. Update rows with some other value and swap and then update to actual value.

But I don't want to go for these as they do not seem to be the appropriate solution to the problem. Could anyone help me out?

Answer 1

The magic word is DEFERRABLE here:

DROP TABLE ztable CASCADE; CREATE TABLE ztable     ( id integer NOT NULL PRIMARY KEY     , payload varchar     ); INSERT INTO ztable(id,payload) VALUES (1,'one' ), (2,'two' ), (3,'three' ); SELECT * FROM ztable;       -- This works, because there is no constraint UPDATE ztable t1 SET payload=t2.payload FROM ztable t2 WHERE t1.id IN (2,3) AND t2.id IN (2,3) AND t1.id <> t2.id     ; SELECT * FROM ztable;  ALTER TABLE ztable ADD CONSTRAINT OMG_WTF UNIQUE (payload)     DEFERRABLE INITIALLY DEFERRED     ;      -- This should also work, because the constraint      -- is deferred until "commit time" UPDATE ztable t1 SET payload=t2.payload FROM ztable t2 WHERE t1.id IN (2,3) AND t2.id IN (2,3) AND t1.id <> t2.id     ; SELECT * FROM ztable; 

RESULT:

DROP TABLE NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "ztable_pkey" for table "ztable" CREATE TABLE INSERT 0 3  id | payload ----+---------   1 | one   2 | two   3 | three (3 rows)  UPDATE 2  id | payload ----+---------   1 | one   2 | three   3 | two (3 rows)  NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "omg_wtf" for table "ztable" ALTER TABLE UPDATE 2  id | payload ----+---------   1 | one   2 | two   3 | three (3 rows)