PostgreSQL trigger to avoid duplicate values in jsonb

Question

I need to check if a value inside a jsonb is already present in a array. I'm trying to achieve this with a trigger but i'm new to this language and I don't know how to write the query.

    CREATE TABLE merchants (
      key uuid PRIMARY KEY,
      data jsonb NOT NULL
    )

Here is the trigger. I think the NEW.data.ids part is wrong.

CREATE FUNCTION validate_id_constraint() returns trigger as $$
    DECLARE merchants_count int;
  BEGIN
    merchants_count := (SELECT count(*) FROM merchants WHERE data->'ids' @> NEW.data.ids);
    IF (merchants_count != 0) THEN
      RAISE EXCEPTION 'Duplicate id';
    END IF;
    RETURN NEW;
  END;
  $$ language plpgsql;

  CREATE TRIGGER validate_id_constraint_trigger BEFORE INSERT OR UPDATE ON merchants
    FOR EACH ROW EXECUTE PROCEDURE validate_id_constraint();

When i insert into the table i get this error message

ERROR:  missing FROM-clause entry for table "data"
LINE 1: ...LECT count(*) FROM merchants WHERE data->'ids' @> NEW.data.i...
                                                            ^

I have done the query outside the trigger and it works fine

   SELECT count(*) FROM merchants WHERE data->'ids' @> '["11176", "11363"]'

Please show the structure of your JSON, particularly the ids. — Laurenz Albe
– Laurenz Albe, Commented Apr 23, 2018 at 6:56
And you only want to error out if the array is contained in an existing array, not if there are overlaps? — Laurenz Albe
– Laurenz Albe, Commented Apr 23, 2018 at 7:05

Laurenz Albe · Accepted Answer · 2018-04-23 08:52:52Z

You get that error because you are using . instead of -> to extract the ids array in the expression NEW.data.ids.

But your trigger won't work anyway because you are not trying to avoid containment, but overlaps in the arrays.

One way you could write the trigger function is:

CREATE OR REPLACE FUNCTION validate_id_constraint() RETURNS trigger
   LANGUAGE plpgsql AS
$$DECLARE
   j jsonb;
BEGIN
   FOR j IN
      SELECT jsonb_array_elements(NEW.data->'ids')
   LOOP
      IF EXISTS
         (SELECT 1 FROM merchants WHERE j <@ (data->'ids'))
      THEN
         RAISE EXCEPTION 'Duplicate IDs';
      END IF;
   END LOOP;
   RETURN NEW;
END;$$;

You have to loop because there is no “overlaps” operator on jsonb arrays.

This is all slow and cumbersome because of your table design.

Note 1: You would be much better off if you only store data in jsonb that you do not need to manipulate in the database. In particular, you should store the ids array as field in the table. Then you can use the “overlaps” operator && and speed that up with a gin index.

You would be even faster if you normalized the table structure and stored the individual array entries in a separate table, then a regular unique constraint would do.

Note 2: Any constraint enabled by a trigger suffers from a race condition: if two concurrent INSERTs conflict with each other, the trigger function will not see the values from the concurrent INSERT and you may end up with inconsistent data.

Collectives™ on Stack Overflow

PostgreSQL trigger to avoid duplicate values in jsonb

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related