Postgres-具有多列和空值的唯一约束

Question

我对Postgres Unique约束有问题，该约束包含多个可能包含空值的列。

让我们假设这种情况：

CREATE TEMP TABLE test (
  foo TEXT,
  bar TEXT,
  UNIQUE (foo, bar)
);

INSERT INTO test
VALUES 
  ('foo', NULL),
  ('foo', NULL),
  ('foo', 'bar'),
  ('foo', 'bar')
ON CONFLICT (foo, bar) DO NOTHING;

Insert将插入（'foo'，bar'）一次和（'foo'，NULL）两次（尽管直觉告诉它应该插入一次）。

在这种情况下，解决方案非常简单。我可以添加唯一的索引

CREATE UNIQUE INDEX indx ON test (foo) WHERE bar IS NULL;

但是当有更多的列和不同的类型（不仅仅是文本）时，问题就开始了。假设我们有10列，其中9列可以有NULL值。也许我可以用大量的限制来解决它，但这一点都不方便。

有没有更简单的方法来保持这样一行的唯一性？

Answer 1

一般来说，您可以考虑列定义，因为两者都被表示为文本，可以让它们不可空，并提供默认值为“（空字符串）”。这样你就可以确定，foo不会被保存两次。此外，空值在实践中也不是很好，因为它只是缺少值的一个标志。

CREATE TEMP TABLE test (
  foo TEXT DEFAULT '' NOT NULL,
  bar TEXT DEFAULT '' NOT NULL,
  UNIQUE (foo, bar)
);

Answer 2

一个替代禁止NULL的好方法是创建一个唯一的索引。

您所需要的只是一个保证不会出现在数据集中的值（在我的示例中，'@'）：

CREATE UNIQUE INDEX ON test (
   coalesce(foo, '@@'),
   coalesce(bar, '@@')
);