Selecting all rows where an array intersects in postgresql

Question

Currently I'm trying, in a rails 3 application, to select all objects where two arrays have some overlap.

I've tried the following:

Contact.where("possible_unique_keys && ?" c.possible_unique_keys)

Which gives:

array value must start with "{" or dimension information

So I tried to convert the latter record into a postgresql array, like so:

Contact.where("possible_unique_keys && string_to_array(?)", c.possible_unique_keys)

Which gives:

operator does not exist: character varying[] && text[]

How so I get both arrays to a format that will correctly evaluate &&?

score 1 · Accepted Answer · answered Jan 31 '13 at 15:22

I think I've solved it for now, and come up with a way to build a query from a Ruby array to a postgresql array (thanks in part to this bit of code written for rails/postgresql interoperability.

Given a list of strings ["bill", "joe", "stu", "katie", "laura"], we have to do a little bit in the way of acrobatics to get postgresql to recognize them. This is a solution to construct the request.

request = "SELECT * from  Contacts where possible_unique_keys && \'{"
list.each do |key|
    key.gsub("'", "\\'")
    if key == c.possible_unique_keys.last
        request << "\"#{key}\"}\'"
    else
        request << "\"#{key}\", "
    end
 end
 dupes = Contacts.find_by_sql(request)

Selecting all rows where an array intersects in postgresql

1 Answers1