插入/更新时的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)

我如何定义排除约束，以便只有一行具有特定条形码id的foo能够将批准的设置为true？

例如，使用以下数据：

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

我将能够将批准行3设置为true，因为没有其他具有foo_id3的行为true。

然而，将第2行的approved更新为true将失败，因为第1行也有foo_id1，并且已经被批准。




Answer 1

试试这个

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

Answer 2

您不需要排除约束，过滤后的唯一索引可以：

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

我还建议将approved定义为notnull。允许null值的布尔列通常会引起持续的混淆。