postgres - comparing two arrays

Question

postgres has an array data type, in this case a numeric array:

CREATE TABLE sal_emp (name text, pay_by_quarter integer[]); INSERT INTO sal_emp VALUES ('one', '{1,2,3}'); INSERT INTO sal_emp VALUES ('two', '{4,5,6}'); INSERT INTO sal_emp VALUES ('three', '{2,4,6}'); SELECT * FROM sal_emp;  Result: one, {1,2,3} two, {4,5,6} three, {2,4,6} 

From what i can tell, you can only query an array as follows:

SELECT * FROM sal_emp WHERE 4=ANY(pay_by_quarter); SELECT * FROM sal_emp WHERE ARRAY[4,5,6]=pay_by_quarter; 

which means you can select a row with the array contains a match for a single argument, or if the whole array matches an array argument.

I need to select a row where any member of the row's array matches any member of an argument array - kind of like an 'IN' but i can't figure out how. I've tried the following two approaches but neither work:

SELECT * from sal_emp WHERE ARRAY[4,5,6]=ANY(pay_by_quarter); SELECT * from sal_emp WHERE ANY(pay_by_quarter) IN (4,5,6); 

I assume i could do something with converting the array to a string but that sounds like poor solution..

any ideas?

Answer 1

figured it ... there's an && operator

http://www.postgresql.org/docs/current/static/functions-array.html

"&& overlap (have elements in common) ARRAY[1,4,3] && ARRAY[2,1]"