Adding condition to Unique Constraint
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.