I need to compare 2 arrays but with taking into account duplicate values counting.
Currently:
select array[1,2,3,3] <@ array[1,2,3]
=> true
select array[1,2,3,3] <@ array[1,2,3,3]
=> true
But what I need:
- array[1,2,3,3] vs array[1,2,3] should be false because array[1,2,3] has a 3 value missing.
- array[1,2,3,3] vs array[1,2,3,3] should be true.
- array[1,2,3,3] vs array[1,2,3,3,3] should be true.
- array[1,2,3,3] vs array[1,2,3,3,4,5] should be true.
=> All values in the left array need to be in the right array, including duplicate values...
Thanks!
EDIT : I found a possible solution:
SELECT ARRAY
(
SELECT UNNEST(a1)
except all
SELECT UNNEST(a2)
)
FROM (
SELECT array[1,2,3,3] AS a1
, array[1,2,3] AS a2
) q;
If result not empty, values on left are not on right.