0

We have a Postgres (9.6) table my_table with a row email_address jsonb[] NOT NULL:

DROP TABLE my_table;
CREATE TABLE my_table (email_address jsonb NOT NULL);

We want to store an array of email addresses as JSONB (for the sake of this example, I'll only keep the address key):

INSERT INTO my_table (email_address)
VALUES (ARRAY[ $${"address": "A"}$$::jsonb,
               $${"address": "B"}$$::jsonb,
               $${"address": "C"}$$::jsonb ]);

INSERT INTO my_table (email_address)
VALUES (ARRAY[ $${"address": "D"}$$::jsonb ]);

We want to make sure that each of the element we add to the array have a unique set of address.

-- ["A", "B"] <@ ["A", "B", "C"], SHOULD FAIL
INSERT INTO my_table (email_address)
VALUES (ARRAY[ $${"address": "A"}$$::jsonb, $${"address": "B"}$$::jsonb ]

-- ["A", "D"] IS UNIQUE SET, SHOULD SUCCEED
INSERT INTO my_table (email_address)
VALUES (ARRAY[ $${"address": "A"}$$::jsonb, $${"address": "D"}$$::jsonb ];

We were thinking about a exclusion constraint using a function maybe? We are currently trying to "map" the jsonb[] into a text[] ([{address: "A"}, {address: "B"}] into ["A", "B"]) to perform a simple @> operation but we struggle to obtain any convincing result.

Our question is: Is such a constraint possible (if yes any help on the code would be greatly appreciated), and if not what would be the best way to implement such a system without having to create another table?

Paul Slm
  • 403
  • 3
  • 11
  • why are you storing these in json, and not just an array of text, `text[]`. Or even better as a [custom e-mail type.](http://dba.stackexchange.com/a/165923/2639) – Evan Carroll Apr 11 '17 at 00:46
  • We have some complementary information to store with this email address as a Boolean field "preferred" and other data that I didn't included in this example to keep it simple. – Paul Slm Apr 11 '17 at 02:35
  • Possible duplicate of [Postgres 9.6 - Create unique index for json array](http://stackoverflow.com/questions/43220068/postgres-9-6-create-unique-index-for-json-array) – pozs Apr 11 '17 at 08:30
  • This is almost exactly the same question as the earlier. It won't work, neither with an exclusion constraint. `@>` is not [commutative](https://en.wikipedia.org/wiki/Commutative_property) so it's inherently wrong for exclusion anyway. The only one which could work is the `&&` for arrays, but that's not supported by GiST at the moment. – pozs Apr 11 '17 at 08:34
  • It's not really the same, one is to index each individual element of an array, which is not the question here. Also I don't want to deal with commutativity, I really want to use `@>` only. – Paul Slm Apr 11 '17 at 16:24
  • Should this fail or succeed? `['A', 'B'] <@ ['A']` – Łukasz Kamiński Apr 12 '17 at 08:06
  • If `['A']` is present in my DB and I attempt to insert `['A', 'B']`, it should successfully insert `['A', 'B']`. I only care about `@>` – Paul Slm Apr 12 '17 at 16:54

0 Answers0