CODEWITHSUNDEEP
jsonpostgresqlunique-constraint
writofmandamus
writofmandamus

Reputation: 1231

How to make an empty JSON object not unique in Postgres?

I am trying to set a unique constraint on a column that can have an empty JSON object {}. I am using Postgres 9.6.3.

The problem is that Postgres is treating them as unique because I can insert multiple rows with that same value. I think it is related to how Postgres treats null values as unique. How can I circumvent this?

Upvotes: 0

Views: 2030

Answers (3)

alzee
alzee

Reputation: 1396

You can use a normal unique index treating the json as text. Here is a complete example showing how it works (Corrected):

Create the table: create table tmp (payload json, name text);

Create our index: create unique index testindex on tmp ((payload::text), name);

Insert some rows. The first four will work, the rest will fail.

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

If something here isn't working as you expect, please clarify.

Upvotes: 1

cske
cske

Reputation: 2243

You need an another partial index on name when json expression is null

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

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

Upvotes: 1

klin
klin

Reputation: 121604

Use the jsonb type, a unique constraint works as expected:

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"

Upvotes: 3

Related Questions