SQL: Unique constraint when column is a certain value

Question

CREATE TABLE foo (    dt     AS DATE NOT NULL,    type   AS TEXT NOT NULL,    CONSTRAINT unique_dt_type UNIQUE(dt,type) -- check constraint(?) ) 

Having a brain-dud when trying to think of the right syntax to create a unique constraint when only a certain condition exists.

Given, type can have values A-F, there can only be one A per date, but there can be multiple B-F. Example of good table:

2010-01-02 | 'A'  -- only one 2010-01-02 | 'B'  -- can have multiple 2010-01-02 | 'B' 2010-01-02 | 'B' 2010-01-02 | 'C'  -- can have multiple 2013-01-02 | 'A'  -- only one 2010-01-02 | 'B'  -- can have multiple 2010-01-02 | 'B' 2013-01-02 | 'F'  -- can have multiple 2013-01-02 | 'F' 

Tried reading check/unique syntax but there weren't any examples. CHECK came close but only limited it to a range and wasn't used in conjunction with a UNIQUE scenario. Also tried searching, but my search skills are either not up to par, or there aren't any similar questions.

Answer 1

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'); 

Answer 2

Using a trigger:

CREATE OR REPLACE FUNCTION "CheckConstraint"()   RETURNS trigger AS $BODY$declare already_exists boolean;  begin  if new.foo_type='A' then select count(*) >0  from foo where foo_type='A' and dt=new.dt INTO already_exists; if already_exists then  raise exception 'date % already have an A', new.dt; end if; end if;  return new; end;$BODY$   LANGUAGE plpgsql VOLATILE   COST 100;