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

Question

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?

score 3 · Answer 1 · answered Aug 09 '17 at 18:02

3

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"

answered Aug 09 '17 at 18:02

klin

112,967
15
204
232

Thanks, this works as well. However, this requires changing the data type so it looks like it takes slightly more work to migrate and seed the data. So given that I can only choose one answer I marked the @cske answer as correct. I'm also a little scared of changing the data type right now in case I break the rest of the code base. – writofmandamus Aug 09 '17 at 20:31
1

I understand your concerns about changing data types, though it is quite simple and rather safe. The use of jsonb is particularly desirable in case you need an index on the column. See also [Explanation of JSONB introduced by PostgreSQL](https://stackoverflow.com/a/22910602/1995738) and [When to use unstructured datatypes in Postgres](https://www.citusdata.com/blog/2016/07/14/choosing-nosql-hstore-json-jsonb/). – klin Aug 09 '17 at 20:56

alzee · Accepted Answer · 2017-08-09T21:06:24.077

1

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.

edited Aug 09 '17 at 21:06

answered Aug 09 '17 at 17:54

alzee

1,393
1
10
21

This does not work, more specifically, I'm setting it up to be unique in a combination of two columns: `create unique INDEX actions_constraint on actions (((payload#>>'{message, payload, content}')::text), name) where payload != '{}';` My two column names are `payload` and `name`, – writofmandamus Aug 09 '17 at 18:03
Are they both actual text fields? If so then it should work just fine, it does for me in a simple temp/test table. – alzee Aug 09 '17 at 18:11
`payload` is type `json` and `name` is type `text`. But yeah `payload->message->payload->>content` will be type `text`. But, we need to handle the case of a creation of a row like: `name: sometext` and payload: '{}'`. – writofmandamus Aug 09 '17 at 18:31
So you're saying when name is set you *do* want `{}` to be treated as unique, e.g. not allowing the same name to be used twice? If that's the case, just make a separate unique index on the name field alone. – alzee Aug 09 '17 at 18:39
Updated answer with complete example. – alzee Aug 09 '17 at 19:24
I only want unique combinations of `name` and `payload`. So, I should not be allowed to create two rows of `name: somename, payload: {}`. So I DON'T want every `{}` to be treated as unique. Currently your code allows me to create these duplicates, which I don't want. Thanks for your answer though, I like how your query is in one line. – writofmandamus Aug 09 '17 at 20:28
Sorry for misunderstanding. In that case, all you have to do is delete the WHERE clause from the index. `create unique index testindex on tmp ((payload::text), name)` will do it. – alzee Aug 09 '17 at 21:03
Thanks, your solution ended up being the most appropriate for me right now :) – writofmandamus Aug 09 '17 at 22:29

cske · Answer 3 · 2017-08-09T19:59:34.240

1

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

edited Aug 09 '17 at 19:59

answered Aug 09 '17 at 18:18

cske

2,233
4
26
24

Thanks, this looks like a great answer. I want to be more explicit though to say when `payload` is equal to `{}`. How is that constructed? This isn't accepted: `create unique INDEX actions_constraint_on_empty on actions (name) where payload == '{}';`. I'm guessing I need some operator on `payload` so it is treated as JSON? – writofmandamus Aug 09 '17 at 18:44
wait, it may be because I'm missing parenthesis around `payload`. – writofmandamus Aug 09 '17 at 18:45
`==` ?? use `=` – cske Aug 09 '17 at 18:46
Thanks, I got thrown off with `!=` so thinking the negation of that would be `==`. Right now `create unique INDEX actions_constraint_on_empty on actions (name) where (payload) = '{}';` gives me this strange error: `FATAL: could not open relation mapping file "global/pg_filenode.map": No such file or directory` – writofmandamus Aug 09 '17 at 19:01
Actually I'm no longer getting the error above but instead `operator does not exist: json = unknown` – writofmandamus Aug 09 '17 at 19:05
`payload = '{}'::json` – cske Aug 09 '17 at 19:06
I tried that and it gave me an error of `error: operator does not exist: json = json` – writofmandamus Aug 09 '17 at 19:09
`create unique INDEX actions_constraint_on_empty on action (name) where (payload::text = '{}') ;` – cske Aug 09 '17 at 19:17
Comparing texts there work. Thanks! Can you change your solution to reflect that so I can mark it as the answer? – writofmandamus Aug 09 '17 at 19:56

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

3 Answers3