3

For a table like:

CREATE TABLE example (
    totally_unique JSONB
);

How can I constrain that values for all keys in totally_unique must be different? The keys and values can be any strings, so I can't just write individual constraints for each possible key.

In other words, if {"a": "one", "b": "two"} is in the table, I want to prevent inserting {"a": "one", "b": "three"} because the value totally_unique->>'a' = 'one' already exists.

A UNIQUE constraint isn't sufficient for this, but I don't see what kind of constraint or index would work.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user1475412
  • 1,659
  • 2
  • 22
  • 30

2 Answers2

6

There is no built-in method to guarantee unique key/value pairs inside JSON values across the table, neither for json nor for jsonb.

But you can achieve your goal with a helper table and an index. Only considering the outermost level of your JSON values. This is not prepared for nested values.

Solution for jsonb

Requires Postgres 9.4 or later, obviously.
(Works for json in Postgres 9.3, too, after minor modifications.)

Table layout

CREATE TABLE example (
  example_id     serial PRIMARY KEY
, totally_unique jsonb NOT NULL  -- NOT json
);

CREATE TABLE example_key (
  key   text
, value text
, PRIMARY KEY (key, value)
);

Trigger function & trigger

CREATE OR REPLACE FUNCTION trg_example_insupdelbef()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   -- split UPDATE into DELETE & INSERT to simplify
   IF TG_OP = 'UPDATE' THEN
      -- Works in pg 9.4+ with jsonb:
      -- Does not work with json since there is no "=" operator for json
      IF OLD.totally_unique IS NOT DISTINCT FROM NEW.totally_unique THEN
         RETURN NEW;  -- exit, nothing to do
      END IF;
   END IF;

   IF TG_OP IN ('DELETE', 'UPDATE') THEN
      DELETE FROM example_key k
      USING  jsonb_each_text(OLD.totally_unique) j(key, value)
      WHERE  j.key = k.key
      AND    j.value = k.value;

      IF TG_OP = 'DELETE' THEN
         RETURN OLD;  -- exit, we are done
      END IF;
   END IF;

   INSERT INTO example_key(key, value)
   SELECT *
   FROM   jsonb_each_text(NEW.totally_unique) j;

   RETURN NEW;
END
$func$;

CREATE TRIGGER example_insupdelbef
BEFORE INSERT OR DELETE OR UPDATE OF totally_unique ON example
FOR EACH ROW EXECUTE PROCEDURE trg_example_insupdelbef();

db<>fiddle here - demonstrating INSERT / UPDATE / DELETE.
Old sqlfiddle

The key function to handle jsonb is jsonb_each_text(), which does exactly what you need, since your values are supposed to be text.

Closely related answer for a Postgres array column with more explanation:

Also consider the "righteous path" of normalization laid out there. Applies here as well.

This is not as unbreakable as a UNIQUE constraint, since triggers can be circumvented by other triggers and more easily deactivated, but if you don't do anything of the sort, your constraint is enforced at all times.

Note in particular that, per documentation:

TRUNCATE will not fire any ON DELETE triggers that might exist for the tables. But it will fire ON TRUNCATE triggers.

If you plan to TRUNCATE example, then make sure you TRUNCATE example_key as well, or create another trigger for that.

Performance should be decently good. If your totally_unique column holds many keys and typically only few change per UPDATE, then it might pay to have separate logic for TG_OP = 'UPDATE' in your trigger: distill a change set between OLD and NEW, and only apply that to example_key.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
5

If the number of keys is determined and reasonably small, you can create partial unique indexes for all keys.

create unique index example_uq_a on example
((totally_unique->'a'))
where (totally_unique->'a') notnull;

create unique index example_uq_b on example
((totally_unique->'b'))
where (totally_unique->'b') notnull;

Some checks:

test=# insert into example values ('{"a": 1, "b": 2}');
INSERT 0 1

test=# insert into example values ('{"a": 1, "b": 1}');
ERROR:  duplicate key value violates unique constraint "example_uq_a"

test=# insert into example values ('{"a": 2, "b": 2}');
ERROR:  duplicate key value violates unique constraint "example_uq_b"

test=# insert into example values ('{"a": 1}');
ERROR:  duplicate key value violates unique constraint "example_uq_a"

test=# insert into example values ('{"b": 2}');
ERROR:  duplicate key value violates unique constraint "example_uq_b"

If the number of keys is not determined, I do not see the possibility of creating constraint. In this case I would create a trigger for insert and update, which would reject values existing in the table.

klin
  • 112,967
  • 15
  • 204
  • 232