1

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.

Sudhir Jonathan
  • 16,998
  • 13
  • 66
  • 90

1 Answers1

5

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);
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228