创建多个索引以强制执行唯一性

Question

我的桌子上有两个字段，如果它们(tat和dim)相等，我就丢弃它们。

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

这被认为不是is、1和2的有效条目。因为我希望允许每个dim每个tat。因此，我有一个脚本来为我清理这些副本，然后我在这个表上添加了索引，以防止这种情况发生：

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

正如这个答案很好地解释的那样：

https://stackoverflow.com/a/8918141/169277

现在在这种情况下更进一步

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)出现更多次？

Answer 1

这似乎是个误会。

您从我的回答中引用的话有点误导，因为它只适用于您还创建了另一个部分索引的情况，如上面所描述的：

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

如果您不添加第二个索引(就像您没有添加)，那么您已经有了解决方案，这似乎是可行的。单独使用多个only唯一索引，您可以多次输入(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时除外)。关于dba.SE的相关答案中的详细信息。