I currently have the following constraint on a table:

            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:

         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.


1 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.

  • Index lacks an option to be mentioned in ON CONFLICT case where only unique keys are accepted. Commented May 19, 2022 at 18:54
  • 3
    @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 ... Commented May 19, 2022 at 18:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.