PostgreSQL – Custom Unique Constraint Enforced Only if One Column Has a Specific Value

constraintdatabase-designindexpostgresqlunique-constraint

Is it possible to have a custom unique constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter).

If type is 'true', then I want the combination of type and subset to be unique. Otherwise, there is no constraint. I'm using PostgreSQL 8.4 on Debian.

Best Answer

In other words, you want values in the column subset to be unique among rows where the column type is 'true'.
A partial unique index will do that:

CREATE UNIQUE INDEX tbl_some_name_idx ON tbl (subset) WHERE type = 'true';

Data type does matter. If the column type is boolean (likely should be), you can simplify:

CREATE UNIQUE INDEX tbl_some_name_idx ON tbl (subset) WHERE type;

This way you can even make combinations with NULL unique, which is not possible otherwise. See:

Related Question