Conditional unique constraint not updating correctly

Question

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

Try to make the unique constraint DEFERRABLE INITIALLY DEFERRED. Not sure for the syntax unfortunately. — Alexandar Mitsev, Jul 25 '16 at 08:31
Seems actually that it is not possible with UNIQUE INDEX, only with CONSTRAINT UNIQUE — Alexandar Mitsev, Jul 25 '16 at 08:35
Use stored function to update the data. `update test set value = false where property='a' and value;` would be more efficient. BTW `update test set value = id = 3 where property = 'a';` is simpler. — Abelisto, Jul 25 '16 at 08:58

score 3 · Accepted Answer · edited May 23 '17 at 11:44

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;

Conditional unique constraint not updating correctly

1 Answers1