UNIQUE constraint where NULL is one valid value [duplicate]

Question

Inn PostgreSQL I want to have a multi-column UNIQUE constraint where one of the columns can be NULL exactly once.

What I've tried so far:

ALTER TABLE customexternalemail 
ADD CONSTRAINT customexternalemail_sp_emaildomain_unique 
    UNIQUE(serviceproviderid, emailprefix, emaildomainid);

Where serviceproviderid and emaildomainid are BIGINT, and emailprefix is TEXT. emaildomainid is the only column with NULL allowed, and is the column I'm having trouble with.

Basically, I want to allow only one entry matching a combination of serviceproviderid, emailprefix, and emaildomainid, where emaildomainid could be a BIGINT value or NULL. Currently (with the above constraint), it will accept repeats if emaildomainid is NULL, but if emaildomainid is not NULL, it must be unique.

Answer 1

You can create two partial indexes. They are supported since version 7.2, which was released in February 2002.

This one will check that any combinaison of the three columns will be unique when emaildomainid isn't null:

CREATE UNIQUE INDEX customexternalemail_sp_emaildomain_unique_not_null 
    ON customexternalemail (serviceproviderid, emailprefix, emaildomainid)
    WHERE emaildomainid IS NOT NULL;

This one will ensure that for any row which has a null value for emaildomainid, the combination (serviceproviderid, emailprefix) will be unique:

CREATE UNIQUE INDEX customexternalemail_sp_emaildomain_unique_null 
    ON customexternalemail (serviceproviderid, emailprefix)
    WHERE emaildomainid IS NULL;