如何在PostgreSQL上添加条件唯一索引

hs1rzwqc 于 2023-06-29 发布在 PostgreSQL

关注(0)|答案(3)|浏览(172)

我有一个line_items表，其中包含以下列：

product_id
variant_id

variant_id可以为空。
条件如下：

这在PostgreSQL中可能吗？

3条答案

在(product_id, variant_id)上创建UNIQUE multicolumn index：

CREATE UNIQUE INDEX line_items_prod_var_idx ON line_items (product_id, variant_id);

但是，默认情况下，这允许(product_id, variant_id)有多个(1, null)条目，因为null值不被认为是不同的值。
为了弥补这一点，在product_id上额外创建一个partial UNIQUE index：

CREATE UNIQUE INDEX line_items_prod_var_null_idx ON line_items (product_id)
WHERE variant_id IS NULL;

这样，您可以输入(1,2)、(1,3)和(1, null)，但不会再次输入。还可以加快对一列或两列进行条件查询的速度。

或者在Postgres 15以上版本中使用**NULLS NOT DISTINCT**子句。参见：

另一种选择是在关键字段中使用表达式。当你问这个问题的时候，这可能还没有出现，但是对于现在遇到这个问题的其他人来说可能是有帮助的。

CREATE UNIQUE INDEX line_items_prod_id_var_id_idx
ON line_items ( product_id, (coalesce(variant_id, 0)) );

当然，这假设您的variant_id是一个从1开始的自动递增整数。还要注意表达式周围的括号。根据文档，它们是必需的。
http://www.postgresql.org/docs/9.3/static/sql-createindex.html

以下也可以工作-

CREATE UNIQUE INDEX line_items_prod_var_idx ON line_items (product_id, coalesce(variant_id,'default'));