2

'Sup,

We have a Postgres (9.6) table myTable with a row data jsonb NOT NULL:

DROP TABLE myTable;
CREATE TABLE myTable (data jsonb NOT NULL);

We want to store object containing an array of email addresses:

INSERT INTO myTable (data) VALUES ($${"email": [{"address": "A"}, {"address": "B"}]}$$);
INSERT INTO myTable (data) VALUES ($${"email": [{"address": "C"}]}$$);

We want to create a unique index on address to prevent insertion of row like:

-- "A" NON UNIQUE, SHOULD FAIL
INSERT INTO myTable (data) VALUES ($${"email": [{"address": "A"}]}$$);

-- "C" NON UNIQUE, SHOULD FAIL
INSERT INTO myTable (data) VALUES ($${"email": [{"address": "C"}, {"address": "D"}]}$$);

We tried using:

-- THROW: "ERROR:  index expression cannot return a set"
CREATE UNIQUE INDEX my_index ON myTable(((jsonb_array_elements((data->>'email')::jsonb)->>'address')));

We are looking for a solution using indexes or constraints, without extra table nor views nor triggers is possible. Any comment or idea would be greatly appreciated.

Cheers,

Paul Slm
  • 403
  • 3
  • 11
  • 2
    You cannot do that with a unique index. You possibly cannot do that with just `jsonb` either. What you *can* do is defining an [exclusion constraint](https://www.postgresql.org/docs/current/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE) over the extracted email addresses as native PostgreSQL arrays (with the [array overlaps operator: `&&`](https://www.postgresql.org/docs/current/static/functions-array.html)). – pozs Apr 05 '17 at 07:48
  • 1
    @pozs I would say this is correct. jsonb doesn't have overlaps operator and <@ or @> will not cover all possible combinations. For example `$${"email": [{"address": "A"}, {"address": "D"}]}$$::jsonb` is not contained nor does not contain `$${"email": [{"address": "A"}, {"address": "B"}]}$$::jsonb` – Łukasz Kamiński Apr 05 '17 at 07:59
  • 2
    Alright, I take it back: unfortunately, arrays have no built-in operator class for neither `gist` nor `spgist`, and `gin` cannot be used for exclusion constraints (and `btree` does not support the overlaps operator `&&` anyway). So, what you want is not possible at all. You could normalize your data instead. – pozs Apr 05 '17 at 08:44
  • Thank you very much for your comments, it does look like this problem is unresolvable with a unique index for now. – Paul Slm Apr 05 '17 at 19:00

0 Answers0