Alter table column to add constraint and where column is not null

I have this:

alter table supplier
add constraint supplier_unique
unique (supplier_id) where supplier_id is not null;

but I am getting an error.

The resulting definition should be:

CREATE UNIQUE INDEX supplier_unique
ON supplier (supplier_id) WHE开发者_StackOverflow中文版RE (supplier_id IS NOT NULL)

Thanks.

A unique constraint in PostgreSQL doesn't have a WHERE clause so you're getting a syntax error at "where". In any case, supplier_id is not null is not necessary with a unique constraint:

In general, a unique constraint is violated when there is more than one row in the table where the values of all of the columns included in the constraint are equal. However, two null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, ...

So all you need is this:

alter table supplier
add constraint supplier_unique
unique (supplier_id);

And your unique constraint will be added, you can have multiple rows with supplier_id IS NULL, and you'll get your index as a side effect of the unique constraint.

However, if you want to create the index directly you can create a partial index with a predicate:

When the WHERE clause is present, a partial index is created. A partial index is an index that contains entries for only a portion of a table, usually a portion that is more useful for indexing than the rest of the table.
[...]
Another possible application is to use WHERE with UNIQUE to enforce uniqueness over a subset of a table.

But in the case of NULLs, a partial index for supplier_id IS NOT NULL won't do anything for the uniqueness because PostgreSQL unique constraints already allow multiple NULL values (probably because of the standard and because x = NULL is false for all x).

So if your intent is too limit the uniqueness to non-NULL values then you don't need your partial index; but, if you just want to avoid indexing NULL values then you could do it through CREATE INDEX (but not ALTER TABLE). I don't know if leaving NULLs out of your index would have any noticeable effect though.

I think part of the confusion (yours and mine) is that UNIQUE is both a constraint and an index. You can include a WHERE when you create it as an index but not when you create it as a constraint. This is certainly inconsistent.