在Postgres中放置复杂的唯一性约束？

Question

在Postgres中，我有一个名为user_badge的表格，包含三列，如下所示。

# select * from user_badge;
 id | user_id | badge_id | is_active |
----+---------+----------+------------
 1    bob       00001      False
 2    bob       00002      True
 3    alice     00003      True
 4    alice     00004      False
 5    alice     00005      False

在这个表中，一个用户可以拥有多个徽章，但每个用户不能拥有超过一个活动徽章（即is_active列为True的行）。

换句话说，查询语句应该...

SELECT user_id FROM user_badge WHERE is_active;

必须返回唯一的行。

这是我希望对此表施加的约束条件。 有没有一种方法可以指定Postgres来保证它？ 在Postgres的约束条件文档中，我看到如何在列和一组列上放置唯一性约束，但没有看到涉及WHERE的唯一性约束的示例。

- mherzl

Answer 1

使用局部或过滤索引：

create unique index unq_user_badge_user_is_active
    on user_badge(user) where is_active;

这在“部分索引”章节的文档中有所描述。

- Gordon Linoff