postgres, contains-operator for multidimensional a

问题:

The following query:

SELECT ARRAY[[1,2,3], [4,5,6], [7,8,9]] @> ARRAY[2, 3, 5];

gets responded with true and not false as expected, since the array[2, 3, 5] doesn't exist in the source array. Any ideas how can it happen? Maybe flatten is applied to multidimensional arrays?

回答1:

It's not answer why, but I've tried to find a way to do what you need, best I've got so far is:

with cte_arr as (
   select ARRAY[[1,2,3], [4,5,6], [7,8,9], [10,11, 12], [2,5,3]] as arr
), cte_s as (
   select generate_subscripts(arr,1) as subscript, arr
   from cte_arr
)
select arr[subscript:subscript] @> ARRAY[2, 3, 5], arr[subscript:subscript]
from cte_s

sql fiddle demo

some useful links about arrays in PostgreSQL:

• Check if value exists in Postgres array
• Convert multi-dimensional array to records
• Unnest array by one level - still unanswered