Custom unique constraint, only enforced if one column has a specific value

Question

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.

Answer 1

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 wasn't possible before Postgres 15. See:

• PostgreSQL multi-column unique constraint and NULL values

Answer 2

This is supplemental to Erwin's answer above, but PostgreSQL supports a bunch of types of indexes. These are not generally mutually exclusive. You can think of these as being:

• Index method (btree, GiST, GIN, etc). Choose one, if necessary (btree being the default)
• Partial or full. If partial use a where clause
• Direct or functional. You can index the output of functions.
• Unique or non-unique

These can all be combined in various ways. All you are doing here is using the unique and partial features, so that gives you partial unique indexes (which are extremely useful as you are finding out.

But suppose you want to have a case insensitive index on the subset field where type is true. Then you would add a functional definition:

CREATE INDEX my_index_name_idx_u ON tbl (lower(subset)) WHERE type;

Note this creates a unique index on the output of the lower() function called on the subset attribute where type is true.