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

Clodoaldo Neto · Accepted Answer · 2014-07-09 22:39:15Z

3

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

answered Jul 9, 2014 at 22:39

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL query for array in any array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related