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.

• Lennart - Slava Ukraini: A partial index is likely the most efficient solution, but you may also be interested in: dba.stackexchange.com/questions/276505/…

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.

• Alex Dvoretsky: Index lacks an option to be mentioned in ON CONFLICT case where only unique keys are accepted.
• Colin 't Hart: @AlexDvoretsky You can! You have to specify the same where that the partial index has after the columns in the on conflict clause. eg ... on conflict(content_id, brand_id) where not is_archived do update ...