3

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}
  • 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} ? – ypercubeᵀᴹ Jul 3 '18 at 8:59
  • @ypercubeᵀᴹ Yes they both should be there. – Harshal Mahajan Jul 3 '18 at 9:00
2

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.