Postgresql updating values between two rows with unique constraint-CodePudding

I am essentially asking the same questions as Switch values between rows in unique column but for PostgreSQL.

My situation is a bit more complex: I have a uniqueness constraint on several columns, let's say columns a and b. I want to exchange values in column b between rows, so for examples

id | a   | b
1  | "a" | 0
2  | "a" | 1
3  | "b" | 0
4  | "b" | 1

The desired result is,

id | a   | b
1  | "a" | 1
2  | "a" | 0
3  | "b" | 1
4  | "b" | 0

I have tried the following natural snippet of code: UPDATE table SET b = CASE WHEN b=0 THEN 1 ELSE 0 END WHERE id <= 4; (the WHERE part is something more complex in my case but it should change nothing).

What I get in the end is that the uniqueness constraint fails, and I don't have any ideas of what else to try.

CodePudding user response：

You need a deferrable constraint, e.g.:

create table my_table(
    id int primary key, 
    a text, 
    b int, 
    unique(a, b) deferrable);

Set the constraint to be deferred in the transaction:

begin;
set constraints all deferred;
update my_table set 
    b = case when b = 0 then 1 else 0 end 
where id <= 4;
commit;

Test it in Db<>Fiddle.

Read in the documentation: CREATE TABLE and SET CONSTRAINTS.

Note, you can define the constraint as deferrable initially deferred, then you do not have to set the constraint before update.