SQL: Unique constraint when column is a certain value

PostgreSQL can address your needs via it’s “Partial Index” feature. In practice this is accomplished by adding a where clause to the create index statement.

Sample:

CREATE INDEX my_partial_ix ON my_sample_table (my_sample_field)
WHERE (my_sample_field = 'rows to index');

Take a look here:
http://www.postgresql.org/docs/current/interactive/indexes-partial.html

Pay particular attention to the section Example 11-3. Setting up a Partial Unique Index. It gives an example that lines up well with your stated objective.

CREATE UNIQUE INDEX my_partial_ix ON my_sample_table (my_sample_field)
WHERE NOT (my_sample_field = 'duplicates ok');