1

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.

4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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