创建多列索引以强制唯一性

一尘不染

sql

我在表中有两个字段，如果它们（tat和dim）相等，则将其丢弃，即

id| tat | dim | visible
1 | 11  | 22  | true
2 | 11  | 22  | false

对于ID1和，这被认为不是有效的条目2。因为我想允许一个dim每tat。因此，我有一个脚本为我清除了这些重复项，然后在此表上添加了索引以防止这种情况发生：

CREATE UNIQUE INDEX dim_tat_idx
ON predictions (tat, dim);

现在在这种情况下走得更远

This way you can enter (1,2), (1,3) and (1, NULL)
but neither of them a second time

我会创造什么类型的索引或其他什么机构，我会用在数据库级别允许进入1, NULL的组合，而且防止(1,2)和(1,3)多次？

阅读 137

2021-05-16

共1个答案

一尘不染

这似乎是一种误解。

您对我的回答的引用有点误导，因为它仅在您还创建额外的部分索引（如此处所述）时才适用：
如何在PostgreSQL上添加条件唯一索引

如果您不添加第二个索引（就像您没有添加的那样）， 那么您 似乎 已经有了解决方案 。单独用多列唯一索引，你可以输入(1, NULL)多次，但(1,2)还是(1,3)只有一次。

如果错误地考虑使用空字符串（''）（对于字符类型）而不是NULL值：这些字符串的处理方式与其他任何值一样。您可以使用 多列，部分功能的唯一索引
（表达式的索引）来处理这种情况：

CREATE UNIQUE INDEX predictions _dim_tat_uni_idx
ON predictions (tat, NULLIF(dim, ''));

这样，您就可以进入(1, 'a')，(1, 'b')只有一次。
但是(1, NULL)又(1, '')多次。

索引仍将完全支持第一列（tat）上的普通查询。
但是，两列上的查询都必须匹配表达式以充分利用潜力。即使看起来没有意义，这样做也会更快：

SELECT * FROM predictions
WHERE  tat = 1
AND    NULLIF(dim, '') = 'foo';

.. 比这个：

SELECT * FROM predictions
WHERE  tat = 1
AND    dim = 'foo';

..因为第一个查询可以使用两个索引列。结果将是相同的（搜索''或时除外NULL）。

2021-05-16