Use of ANY function for multiple values

Question

I am trying to use the ANY function of PostgreSQL to search the value from array interger type column.

My SQL:

SELECT
 *
FROM
 company_employee_contacts
WHERE
 corporate_complaint_type_ids = ANY(ARRAY[1,3]::integer[])

But it is giving me below error:

ERROR: operator does not exist: integer[] = integer

Can anyone tell me why I am getting this error while I am typecasting it?

Expected Result should be like:

id | corporate_complaint_type_ids
----------------------------------
3212 | {1,3}
3216 | {1}
3218 | {3}
3220 | {1,2,3,4}
3221 | {3,4,5}

• ypercubeᵀᴹ: If the column has a value with {1,2,3,4} should it be in the result or not? And one with {3,4,5} ?
• Harshal Mahajan: @ypercubeTM Yes they both should be there.

Answer 1

The reason for the error is that your code compares an array to an integer, i.e. the array in the column with any of the values in the array [1,3].

From the comments, it seems that the wanted result is when the two arrays (column and given array) have at least one common item, i.e. they overlap. The operator for "overlap" is &&:

SELECT
 *
FROM
 company_employee_contacts
WHERE
 corporate_complaint_type_ids && ARRAY[1,3] ;

For more operators on arrays, see the documentation: Array Functions and Operators

---

The idea of using ANY is good but you need a "double ANY" and this syntax would work (if it was valid):

WHERE -- not valid syntax
 ANY(corporate_complaint_type_ids) = ANY(ARRAY[1,3]) ;

You could unfold both arrays or one of the two (using unnest() function) and combine it with ANY. This would be equivalent to the overlaps operator and work - although I see no reason to use something so complicated:

-- unfold with unnest and ANY
WHERE
 EXISTS
 ( SELECT 1 
 FROM unnest(corporate_complaint_type_ids) AS u
 WHERE u = ANY(ARRAY[1,3])
 ) ;
-- or the reverse
WHERE
 EXISTS
 ( SELECT 1 
 FROM unnest(ARRAY[1,3]) AS u
 WHERE u = ANY(corporate_complaint_type_ids)
 ) ;
-- or both with unnest
WHERE
 EXISTS
 ( SELECT 1 
 FROM unnest(corporate_complaint_type_ids) AS u1,
 unnest(ARRAY[1,3]) AS u2 
 WHERE u1 = u2
 ) ;

Answer 2

Or just use contains?

ARRAY[1,4,3] @> ARRAY[3,1]

This is true if array on the left side contains all elements of array on the right.
https://www.postgresql.org/docs/current/functions-array.html