SQL: Unique constraint when column is a certain value

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;

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

Tags:

Sql

Postgresql

Recent Posts