I want to achieve the same behavior as in next example:
select array[1, 3] && array[1] and array[1, 3] && array[2]
using the ALL construct like so:
select array[1, 3] && all(select array[array[1], array[2]])
By logic both queries should return false, but only the first does, while the second query returns true.
Can anybody explain this behavior and hint me how to fix it?
Instead of passing a single multi-dimensional array, pass a set of 1-dimensional arrays - or really any dimensions, as dimensions are just noise in this case. This works:
SELECT ARRAY[1,3] && ALL ((SELECT ARRAY[1] UNION ALL SELECT ARRAY[2])); -- false
Which is more useful when applied to a table column:
SELECT ARRAY[1,3] && ALL ((SELECT array_column FROM some_tbl));
The ALL or ANY constructs either expect a set or an array to the right. Your example only works with a set. I can see how one might conclude to pass a multi-dimensional array (an array of arrays). Unfortunately, that's not how it works. The manual:
The left-hand expression is evaluated and compared to each element of the array [...],
Bold emphasis mine. Element. Not sub-array or array-slice. So the attempt:
SELECT ARRAY[1,3] && ALL (ARRAY[ARRAY[1], ARRAY[2]]); -- error
-- identical, more explicit:
SELECT '{1,3}'::int[] && ALL ('{{1},{2}}'::int[]); -- error
... results in this error message accordingly:
ERROR: operator does not exist: integer[] && integer
You tried something in between:
SELECT ARRAY[1,3] && ALL(SELECT ARRAY[ARRAY[1], ARRAY[2]]); -- true
-- identical, more explicit:
SELECT '{1,3}'::int[] && ALL (SELECT '{{1},{2}}'::int[]); -- true
Effectively a set containing single multi-dimensional array. It boils down to:
SELECT '{1,3}'::int[] && '{{1},{2}}'::int[]; -- true
... which yields true like you observed because, again, "overlap" is tested on the level of elements, ignoring dimensions. Effectively the same as:
SELECT '{1,3}'::int[] && '{1,2}'::int[]; -- true
Or, to prove my point:
SELECT '{{{1}},{{3}}}'::int[] && '{{1},{2}}'::int[]; -- true
Related:
Based on Erwin's answer here is a hack that you can use if you are able to represent inner arrays as strings:
select array[1, 3] && all(
select string_to_array(unnest(array['1,2', '4,3']), ',')::int[]
)