If you’re getting a PostgreSQL error that reads “searching for elements in multidimensional arrays is not supported“, it’s probably because you’re trying to perform an operation against a multi dimensional array when that operation only supports one dimensional arrays.
To fix this issue, perform the operation against a one dimensional array instead. You may be able to do this by applying the unnest()
function against the multi dimensional array.
Example of Error
Here’s an example of code that produces the error:
SELECT array_position(
ARRAY[ ARRAY[ 'Cat', 'Dog', 'Horse' ], ARRAY[ 'Cow', 'Bat', 'Ant' ] ],
'Dog'
);
Result:
ERROR: searching for elements in multidimensional arrays is not supported
Here I used the array_position()
function to search an array, but that function only works on one dimensional arrays. As expected, we get an error.
Solution
One solution is to only include a one dimensional array in our code:
SELECT array_position(
ARRAY[ 'Cat', 'Dog', 'Horse' ],
'Dog'
);
Result:
2
Another way we could deal with the issue is with the unnest()
function:
SELECT array_position(
(SELECT ARRAY(
SELECT unnest((ARRAY[[ 'Cat', 'Dog', 'Horse' ], [ 'Cow', 'Bat', 'Ant' ]])[1:2])
)),
'Bat'
);
Result:
5