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? — nbk
– nbk, Commented Jun 12, 2022 at 21:26

klin · Accepted Answer · 2022-06-12 21:46:45Z

4

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.

edited Jun 12, 2022 at 21:46

answered Jun 12, 2022 at 21:29

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql updating values between two rows with unique constraint

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related