PostgreSQL query for array in any array

Question

I need a PostgreSQL command that matches from a list of emails against an array type column.

my column is declared as this:

emails character varying(255)[] DEFAULT '{}'::character varying[]

And I need to search against it using one of many potential matches. Normally I'd search using the IN operator like so: SELECT * FROM identities WHERE emails IN ['[email protected]']; but I can't seem to find an example of how to generate an IN query when searching against arrays.

Ideally it'd be something like this (which clearly doesn't work):

SELECT * FROM identities WHERE ('[email protected]','[email protected]') IN ANY (emails);

Answer 1

The overlap && operator will check if there are elements in common

SELECT * 
FROM identities 
WHERE array['[email protected]','[email protected]']::varchar[] && emails;

http://www.postgresql.org/docs/current/static/functions-array.html#ARRAY-OPERATORS-TABLE