Skip to content
Advertisement

Remove one, non-unique value from a 2d array

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.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement