I'm writing a Rails 4 app and have a string array column on one of my models called identifiers. When the user passes in a list of identifiers, what's the syntax to fetch the rows where any of the given identifiers match any of the stored identifiers?
I'm trying where('ARRAY[?] == any(identifiers)', ids), but that doesn't seem to work.
Use the array operator "overlap": &&
SELECT * FROM tbl WHERE ARRAY[1,2,3] && identifiers
Or:
SELECT * FROM tbl WHERE '{1,2,3}'::int[] && identifiers
You did not disclose the exact type, which must match, of course. My example is for integer arrays.
This form can utilize a GIN index on the identifiers column:
CREATE INDEX tbl_identifiers_gin_idx ON tbl USING GIN (identifiers);
