'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,