Postgresql updating values between two rows with unique constraint

Question

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.

Does this answer your question? [How to drop a unique constraint on a column in Postgres?](https://stackoverflow.com/questions/51520005/how-to-drop-a-unique-constraint-on-a-column-in-postgres) — nbk, Jun 12 '22 at 21:26

klin · Accepted Answer · 2022-06-12T21:46:45.143

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.

Postgresql updating values between two rows with unique constraint

1 Answers1