0

I have a simple table:

create table item (
  lan      text not null,
  disabled date
);

My aim is to ensure uniqueness for the lan column over all rows, where disabled is null.

I created a partial index:

create unique index lan_idx on item (lan) where disabled is not null;

Now I tried to add the index to the table, but I am struggling with the syntax. (I took the code from this tutorial)

alter table item add constraint lan_idx using index lan_idx;

This throws an syntax error before "using".

I also tried the following:

alter table item add constraint lan_idx unique using index lan_idx;

But this throws the error, that a unique constraint can not be created with a partial index.

Can anybody tell me what I did wrong?

  • You can't define a unique constraint with a partial index. But why do you want to do that? The unique index already gives you that behaviour. – a_horse_with_no_name Apr 17 at 11:50
  • lan should be unique only for those rows, where disabled is null. – ceving Apr 17 at 11:52
  • Which your unique index already enforces - so why do you want an additional constraint? – a_horse_with_no_name Apr 17 at 12:00
  • I do not need to create a constraint? The creation of the index is enough? – ceving Apr 17 at 12:01
  • Yes, the index is enough – a_horse_with_no_name Apr 17 at 12:02
1

You can't define a unique (or primary key) constraint based on a partial index.

However, to enforce uniqueness of the lan column for all non-deleted rows, the partial unique index you already have is enough.

There is no need to add a constraint.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.