SQL: уникальное ограничение, когда столбец является определенным значением

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

Имея мозговой шок, пытаясь думать о правильном синтаксисе, чтобы создать уникальное ограничение, когда существует только определенное условие.

Учитывая, что type может иметь значения A-F, может быть только один A за дату, но может быть несколько B-F. Пример хорошей таблицы:

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'

Пробовал проверку чтения/уникальный синтаксис, но примеров не было. CHECK приблизился, но ограничил его только диапазоном и не использовался в сочетании с сценарием UNIQUE. Также попробовал поиск, но мои навыки поиска либо не совпадают, либо нет похожих вопросов.

Ответ 1

PostgreSQL может удовлетворить ваши потребности с помощью функции "Частичный индекс". На практике это достигается добавлением предложения where в оператор создания индекса.

Образец:

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

Посмотрите здесь: http://www.postgresql.org/docs/current/interactive/indexes-partial.html

Обратите особое внимание на раздел Example 11-3. Setting up a Partial Unique Index. Это пример, который хорошо согласуется с вашей заявленной целью.

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

Ответ 2

Использование триггера:

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;