ActiveRecord - Search Postgres array with multiple values?

Question

From this tip, i see I can search a Postgres array with a singular value so:

User.where("'[email protected]' = ANY (emails)")

I was wondering, however, how I could search for multiple values, for example:

User.where("['[email protected]', '[email protected]'] = ANY (emails)")

which results in a PG::SyntaxError

I am using Activerecord 4.1.1

have you tried ('[email protected]', '[email protected]') ? — Малъ Скрылевъ
– Малъ Скрылевъ, Commented Jul 21, 2014 at 10:57
Yes, I get ActiveRecord::StatementInvalid: PG::DatatypeMismatch: ERROR: argument of WHERE must be type boolean, not type record — waffl
– waffl, Commented Jul 21, 2014 at 11:04
Try then where("ANY (emails) IN ('[email protected]', '[email protected]'))" or then where("IN ('[email protected]', '[email protected]') ANY (emails))" — Малъ Скрылевъ
– Малъ Скрылевъ, Commented Jul 21, 2014 at 11:12
:( The first one seems to run forever until interrupted, producing no results, while the second produced ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near "IN" — waffl
– waffl, Commented Jul 21, 2014 at 13:10

Craig Ringer · Accepted Answer · 2014-07-21 13:36:50Z

1

You want the PostgreSQL array-overlaps or array-contained-by operators, depending on whether you want AND or OR-like behaviour.

SELECT ARRAY['[email protected]', '[email protected]'] @@ emails FROM ...

or

SELECT ARRAY['[email protected]', '[email protected]'] <@ emails FROM ...

Making Rails emit PostgreSQL array literals is hopefully fairly easy, but beyond my Rails-fu of nearly nil.

If emails is of type varchar[] instead of text[] then you need to cast the array literal to varchar too, e.g.

SELECT ARRAY['[email protected]', '[email protected]']::varchar[] <@ emails FROM ...

To test out the functionality stand-alone, you can just evaluate standalone expressions, e.g.

regress=> SELECT ARRAY['[email protected]', '[email protected]'] <@ ARRAY['[email protected]', '[email protected]', 'fred'];
 ?column? 
----------
 t
(1 row)

edited Jul 21, 2014 at 13:36

answered Jul 21, 2014 at 11:42

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

waffl Over a year ago

Hi Craig, a call to ActiveRecord::Base.connection.execute(sql) would return an array, but I'm confused about how to even get this raw sql statement to work. Generally speaking, I'm getting HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. when trying to execute something along the lines of SELECT ARRAY['[email protected]', '[email protected]'] <@ emails FROM users;

Craig Ringer Over a year ago

@waffl works for me - SELECT ARRAY['[email protected]', '[email protected]'] <@ ARRAY['[email protected]', '[email protected]', 'fred'];. What data type is emails? (The first half of the message, which you left out, usually tells you this). Maybe you need a cast; Pg doesn't implicitly cast array types. Or maybe you're on an old version?

waffl Over a year ago

When I try to run the statement without FROM, I get missing FROM-clause entry for table... The field is character varying(255)[]

Craig Ringer Over a year ago

@waffl OK, so you have to cast the input array to varchar[], e.g. ARRAY['a', 'b']::varchar[].

Collectives™ on Stack Overflow

ActiveRecord - Search Postgres array with multiple values?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related