CODEWITHSUNDEEP
postgresql
Gabriel Furstenheim
Gabriel Furstenheim

Reputation: 3432

Conditional unique constraint not updating correctly

I need to enforce uniqueness on a column but only when other column is true. For example:

create temporary table test(id serial primary key, property character varying(50), value boolean);
insert into test(property, value) values ('a', false);
insert into test(property, value) values ('a', true);
insert into test(property, value) values ('a', false);

And I enforce the uniqueness with a conditional index:

create unique index on test(property) where value = true;

So far so good, the problem arises when I try to change the row that has the value set to true. It works if I do:

update test set value = new_value from (select id, id=3 as new_value from test where property = 'a')new_test where test.id = new_test.id

But it doesn't when I do:

update test set value = new_value from (select id, id=1 as new_value from test where property = 'a')new_test where test.id = new_test.id

And I get:

ERROR:  duplicate key value violates unique constraint "test_property_idx"
DETAIL:  Key (property)=(a) already exists.

********** Error **********

ERROR: duplicate key value violates unique constraint "test_property_idx"
SQL state: 23505
Detail: Key (property)=(a) already exists.

Basically it works if the row with value true has a primary key with a bigger value than the current row which is truthy. Any idea on how to circumvent it?

Of course I could do:

update test set value = false where property='a';
update test set value = true where property = 'a' and id = 1;

However, I'm running these queries from node and it is preferable to run only one query.

I'm using Postgres 9.5

Upvotes: 2

Views: 693

Answers (1)

pozs
pozs

Reputation: 36244

Your problem is that UPDATE statements cannot have an ORDER BY clause in SQL (it can have in some RDBMS, but not in PostgreSQL).

The usual solution to this is to make the constraint deferrable. But you use a partial unique index & indexes cannot be declared as deferrable.

Use an exclusion constraint instead: they are the generalization of unique constraints & can be partial too.

ALTER TABLE test
  ADD EXCLUDE (property WITH =) WHERE (value = true)
  DEFERRABLE INITIALLY DEFERRED;

Upvotes: 3

Related Questions