How to add a constraint to a table using a partial index?

Question

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?

• user1822: 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.
• ceving: lan should be unique only for those rows, where disabled is null.
• user1822: Which your unique index already enforces - so why do you want an additional constraint?
• ceving: I do not need to create a constraint? The creation of the index is enough?
• user1822: Yes, the index is enough

Answer 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.