Postgresql – way to enforce uniqueness of arbitrary key-value pairs in a JSONB object column

postgresql

I use Postgres JSONB columns to store translations for dynamic content of a multi-language site — locales are keys, translated strings are values ({"en": "Hello", "ru": "Привет"}).

There're some rare cases when I want to ensure that each translation is unique per locale. I know that I can create an index for each locale:

CREATE UNIQUE INDEX ON categories ((title ->> 'en'));
CREATE UNIQUE INDEX ON categories ((title ->> 'ru'));

This solution has one downside that I would need to create a new index for every column which has such constraint if I decide to add a new locale. Is there any way to specify a uniqueness constraint that will hold for every key-value pair of a JSONB object?

EDIT 1 (in response to the Evan's answer). There seems to be some sort of confusion cased by the term "unique/duplicate translation per locale", so I will try to clarify it. "Unique translation per locale" means: for every locale of each record in the table, there is no other record that has exactly the same translation for that locale. "Locale" is the key of the JSONB object that holds translations. "Translation" is the value of the JSONB object.

Here are some examples: the code 

INSERT INTO categories(title) VALUES
  ('{"en": "Hello"}'),
  ('{"en": "Hello"}');

should yield a uniqueness constraint violation error because it has duplicate "Hello" translation for the en locale, while the code

INSERT INTO categories(title) VALUES
  ('{"en": "Hello"}'),
  ('{"en": "Hi"}');

should not, because it has different translations for the en locale.

Best Answer

Failed attempt with Exclusion Constraints

I don't believe this is possible to do this with exclusion DDL because I don't believe PostgreSQL supports exclusion constraints on text[]

CREATE TABLE categories(title)
AS VALUES
  ('{"en": "Hello", "ru": "Привет"}'::jsonb);

Helper function

CREATE FUNCTION jsonb_object_keys_as_array(j JSONB)
RETURNS text[]
AS $$
  SELECT ARRAY( SELECT jsonb_object_keys(j) AS d );
$$
LANGUAGE sql
IMMUTABLE;

Exclusion constraint attempt.

ALTER TABLE categories
  ADD EXCLUDE
  USING gist (jsonb_object_keys_as_array(title) WITH &&);

ERROR:  data type text[] has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

I'm asking a more specific question on this right now.

Other solutions

As it pertains to your problem, I believe currently you'll have to resort to a less than optimal solution like,

  • Implement this with triggers.
  • Create a function that may or may not use a table or enum to resolve text -> int and then map over text[] returning int[] and use intarray which will permit a GIST index and &&

Or, of course, you can just fix your schema.

Trigger

This method should also be able to make use of a GIST index on catagories(title)

CREATE OR REPLACE FUNCTION resist_duplicate_languages()
RETURNS TRIGGER
AS $$
  DECLARE
    collision bool DEFAULT false;
  BEGIN
    EXECUTE
      FORMAT('SELECT true FROM %I.%I WHERE ctid <> $1 AND title ?| jsonb_object_keys_as_array($2) LIMIT 1', TG_TABLE_SCHEMA, TG_TABLE_NAME )
      INTO collision
      USING NEW.ctid, NEW.title;

    IF collision IS true THEN
      RAISE EXCEPTION 'Can not collide on language %.%(title) languages %', TG_TABLE_SCHEMA, TG_TABLE_NAME, jsonb_object_keys_as_array(NEW.title);
    END IF;

    RETURN null;
  END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER resist_duplicate_languages
  AFTER INSERT OR UPDATE OF title
  ON categories
  FOR EACH ROW
  EXECUTE PROCEDURE resist_duplicate_languages();

Testing

//works
INSERT INTO categories VALUES ('{"fr":"We surrender and eat cheese"}');

//does not work a second time
INSERT INTO categories VALUES ('{"fr":"We surrender and eat cheese"}');
ERROR:  Can not collide on language public.categories(title) languages {fr}
Related Question