如何使一个空的JSON对象在Postgres中不唯一？

问题描述：

我想设置一个唯一的约束可以有一个空的JSON对象的列{}。我正在使用Postgres 9.6.3。如何使一个空的JSON对象在Postgres中不唯一？

问题是，Postgres将它们视为独一无二的，因为我可以插入具有相同值的多行。我认为这与Postgres如何将空值视为唯一对象有关。我怎样才能绕过这个？

答

您可以使用正常的唯一索引，将json当作文本处理。这里是在运行方式（更正）一个完整的例子：

创建该表： create table tmp (payload json, name text);

创建索引： create unique index testindex on tmp ((payload::text), name);

插入一些行。前四项将起作用，其余将失败。

insert into tmp (payload, name) values ('{}', 'foo'); 
// Succeeds 
insert into tmp (payload, name) values ('{}', 'bar'); 
// Succeeds 
insert into tmp (payload, name) values ('{"a":"b"}'::json, 'foo'); 
// Succeeds 
insert into tmp (payload, name) values ('{"a":"b"}'::json, 'bar'); 
// Succeeds 
insert into tmp (payload, name) values ('{"a":"b"}'::json, 'foo'); 
// Fails due to index 
insert into tmp (payload, name) values ('{}', 'bar'); 
// Fails due to index

如果这里的某些内容不能按照您的预期工作，请澄清。

这确实需要一个对名称的另一个部分索引不工作，更具体地说，我将它设置为在两列组合中是唯一的：“创建唯一的INDEX actions_constraint on actions（（（payload＃>>'{message，有效载荷，内容}'）:: text），name）有效载荷！='{}';'我的两列名称是'有效载荷'和'名称'， – writofmandamus

它们是否都是实际文本字段？如果是这样，那么它应该工作得很好，它对我来说只是一个简单的临时/测试表。 – alzee

'payload'是类型'json'，'name'是类型'text'。但是，有效载荷 - >消息 - >有效载荷 - >>内容将被键入“文本”。但是，我们需要处理创建如下行的情况：'name：sometext'和payload：'{}''。 – writofmandamus

答

使用jsonb类型，唯一约束按预期工作：

create table my_table(
    id serial primary key, 
    jdata jsonb unique 
); 

insert into my_table (jdata) 
values 
    ('{}'), 
    ('{}'); 

ERROR: duplicate key value violates unique constraint "my_table_jdata_key"

谢谢，这也适用。但是，这需要更改数据类型，因此它看起来需要稍微更多的工作来迁移和种子数据。所以鉴于我只能选择一个答案，我将@cske答案标记为正确。我现在也有点害怕改变数据类型，以防我破坏其余的代码库。 – writofmandamus

我理解你对改变数据类型的担忧，尽管它非常简单而且相当安全。如果您需要列上的索引，那么使用jsonb是特别需要的。另请参阅[PostgreSQL引入的JSONB的说明]（https://stackoverflow.com/a/22910602/1995738）和[何时使用Postgres中的非结构化数据类型]（https://www.citusdata.com/blog/2016/ 7月14日/选择的NoSQL-hstore-JSON-jsonb /）。 – klin

答

当JSON表达式为空

CREATE TABLE action (
    id BIGSERIAL PRIMARY KEY, 
    name text, 
    payload json 
); 

create unique INDEX actions_constraint on action (((payload#>>'{message, payload, content}')::text), name); 

insert into action(name,payload) values ('a','{}');--works 
insert into action(name,payload) values ('a','{}');--works 

create unique INDEX actions_constraint_on_empty on action (name) where (payload::text = '{}'); 
--fails 
truncate action; 

create unique INDEX actions_constraint_on_empty on action (name) where (payload::text = '{}'); 
--works 

insert into action(name,payload) values ('a','{}'); 
--works 
insert into action(name,payload) values ('a','{}'); 
--fails

检查https://dba.stackexchange.com/questions/9759/postgresql-multi-column-unique-constraint-and-null-values

谢谢，这看起来像一个很好的答案。我希望更明确地说，'payload'等于'{}'时。这是如何构建的？这是不被接受的：'在动作（名称）上创建唯一的INDEX actions_constraint_on_empty，其中payload =='{}';'。我猜我需要一些运算符'有效载荷'，所以它被视为JSON？ – writofmandamus

等待，这可能是因为我在'有效负载'周围遗漏了括号。 – writofmandamus

'=='??使用'=' – cske

如何使一个空的JSON对象在Postgres中不唯一？

相关推荐