> UPDATE sometable SET unique_col = > CASE WHEN unique_col = firstvalue THEN secondvalue > ELSE firstvalue > END > WHERE unique_col = firstvalue > OR unique_col = secondvalue
(See last comment) > BEGIN; > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; > > UPDATE sometable SET unique_col = firstvalue WHERE unique_col = secondvalue; > UPDATE sometable SET unique_col = secondvalue WHERE unique_col = firstvalue; > > COMMIT; This one will always fail unless you DEFER unique constraints -- something we don't support with PostgreSQL, but some others do. > How can I interchange two values in a unique column? Am I missing something > really > obvious (like a swap statement)? Is there any reason besides performance for > not > making index accesses fully ACID-compliant? Doesn't MVCC require this > anyway? The first is what you want. PostgreSQL needs some work in the evaluation of unique indexes to properly support it. Namely, when it sees a conflict when inserting into the index, it needs to record the fact, and revisit the conflict at the end of the command. Lots of work... -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc
signature.asc
Description: This is a digitally signed message part
