Adding condition to Unique Constraint

Question

I currently have the following constraint on a table:

ALTER TABLE myTable
            ADD CONSTRAINT unique_row UNIQUE (content_id, brand_id, language_id);

I want to add a condition to the constraint: I want the constraint to work the same way, but with only a single row where is_archived is FALSE:

ALTER TABLE myTable
         ADD CONSTRAINT unique_row UNIQUE (content_id, brand_id, language_id, !is_archived);

However, when I do this I get a syntax error.

If is_archived = true then multiple rows with the same combination of content_id and brand_id are allowed. Basically multiple rows that are the same can be archived, but only one can be unarchived.

Answer 1

While Postgres doesn't allow a partially unique constraint, it does support a partial unique index:

create unique index unique_row on myTable(content_id, brand_id) where not is_archived;

See Partial Indexes in the Postgres documentation.

This is effectively pretty much the same as a unique constraint, because such constraints are implemented with unique indexes anyway.