JSONB空字段上的唯一约束

Question

让我用一个简单的例子来描述我的问题。假设我有一个表t，它只有两个字段，auto"id"和一个jsonb类型的"info"字段：

CREATE TABLE t(
ID serial NOT NULL PRIMARY KEY,
info jsonb NOT NULL
);

"info"可能包含字段"a"、"b"、"c"以及其他一些字段。字段"c"可能为null，我希望(a，b，c(是唯一的，所以我在它们上创建了一个唯一的索引：

CREATE UNIQUE INDEX idx_abc ON t (
(info ->> 'a'), (info ->> 'b'), (info ->> 'c')
);

但后来我意识到，如果"c"为null，那么这个唯一的约束就根本不起作用。例如，我可以插入多行：

insert into t(info) values('{"a": "a1", "b": "b1"}')

或

insert into t(info) values('{"a": "a1", "b": "b1", "c": null}')

我目前的解决方案是无论如何给"c"一个值，如果"c"为null，那么我设置"null"：

insert into t(info) values('{"a": "a1", "b": "b1", "c": "null"}')

如果我运行插入两次，这种方式将触发唯一约束。但我觉得这只是一个变通方法，我想知道是否有更好的解决方案，即使字段"c"为空，也可以允许唯一约束工作。

Answer 1

如果这只是一个可能具有NULL值的键，那么部分索引将是一个有效的解决方案，如下所示：

• 使用空列创建唯一约束

更通用(效率较低(的解决方案是使用COALESCE:

CREATE UNIQUE INDEX idx_abc ON t (
COALESCE(info ->> 'a', 'NULL')
, COALESCE(info ->> 'b', 'NULL')
, COALESCE(info ->> 'c', 'NULL')
);

通过这种方式，您可以将实际的NULL值和字符串"NULL"(任意示例(折叠为相同的值。就像您已经做的一样，但您不需要处理原始列值。

我也会考虑从jsonb文档中提取相关的键作为常规Postgres列(冗余或不冗余(，以便拥有完整的关系工具库：(NOT NULL(约束、默认值等。速度也更快。