Ограничение исключения Postgres при вставке / обновлении

Question

У меня есть такая таблица

                                     Table "public.foo"
  Column  |  Type   | Collation | Nullable |               Default               
----------+---------+-----------+----------+-------------------------------------
 foo_id   | integer |           | not null | nextval('foo_foo_id_seq'::regclass)
 bar_id   | integer |           |          | 
 approved | boolean |           |          | 
Indexes:
    "foo_pkey" PRIMARY KEY, btree (foo_id)
Foreign-key constraints:
    "foo_bar_id_fkey" FOREIGN KEY (bar_id) REFERENCES bar(bar_id)

Как бы мне определить ограничение исключения, чтобы только одна строка foo с конкретным bar_id сможет установить значение true?

Например со следующими данными:

       foo_id | bar_id | approved 
--------+--------+----------
      1 |      1 | t
      2 |      1 | 
      3 |      2 | 
(3 rows)

Я мог бы установить для строки 3 значение true, потому что ни одна другая строка с 3 не имеет значения true для утверждения.

Однако обновление строки 2 approved значение true не будет выполнено, потому что строка 1 также имеет foo_id 1 и уже утвержден.

Answer 1

Вам не нужно ограничение исключения, подойдет отфильтрованный уникальный индекс:

      create unique index only_one_approved_bar 
   on foo (bar_id)
   where approved;

Я бы также рекомендовал определить approved в виде not null. Логические столбцы, которые позволяют null значения обычно являются источником постоянной путаницы.

Answer 2

попробуй это

      ALTER TABLE public.foo
ADD CONSTRAINT uniq_approved UNIQUE (bar_id, approved)

или вы можете создать уникальный индекс

      CREATE UNIQUE INDEX uniq_approved ON public.foo
USING btree (bar_id, approved)
WHERE approved