Postgresql – Use of ANY function for multiple values

datatypespostgresqlwhere

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}

Best Answer

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
    ) ;