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?
because corporate_complaint_type_ids is not integer, but rather array of integers... You can't:
select '{2,3,4}'::int[] = ANY(ARRAY[1,3]::integer[]);
ERROR: operator does not exist: integer[] = integer
LINE 1: select '{2,3,4}'::int[] = ANY(ARRAY[1,3]::integer[]);
instead you can check if arrays overlap:
postgres@pond93# select '{2,3,4}'::int[] && ARRAY[1,3]::integer[];
?column?
----------
t
(1 row)
or you can check one array value against ANY(array):
postgres@pond93# select ('{2,3,4}'::int[])[1] = ANY(ARRAY[1,3]::integer[]);
?column?
----------
f
(1 row)
corporate_complaint_type_idsis not integer, but rather an array