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?
