To expand on my answered question here:
Remove one, non-unique value from an array
Given this table in PostgreSQL 9.6:
CREATE TABLE test_table ( id int PRIMARY KEY , test_array text[][] );
With a row like:
INSERT INTO test_table (id, test_array) VALUES (1 , '{ {A,AA},{A,AB},{B,AA},{B,AB} }');
How would I remove an index from test_array:
a) matching the [0] value,
b) matching both the [0] and [1] values.
I am getting an exception when using array_position:
searching for elements in multidimensional arrays is not supported
Also, how would an update query be constructed based on this matching?
I’m not sure that I can build a query as done in a 1d array. Any help is appreciated.
Advertisement
Answer
Decided to normalize instead (in this instance, breaking the arrays into two tables with reference keys), per a_horse_with_no_name’s recommendation.