How do you query postgres arrays in ruby on rails for integers using a variable?

Question

Notification.where("uip  @> ?", '{1}')

Works fine and returns all the notifications whose uip array contain a 1.

If I try the following with a variable, however, I have no such luck:

ip = 1

Notification.where("uip  @> ?", '{ip}')

Return the error:

Notification Load (1.8ms)  SELECT "notifications".* FROM "notifications"  WHERE (uip  @> '{ip}')
PG::InvalidTextRepresentation: ERROR:  invalid input syntax for integer: "ip"
LINE 1: ...otifications".* FROM "notifications"  WHERE (uip  @> '{ip}')
                                                                ^
: SELECT "notifications".* FROM "notifications"  WHERE (uip  @> '{ip}')
ActiveRecord::StatementInvalid: PG::InvalidTextRepresentation: ERROR:  invalid input syntax for integer: "ip"
LINE 1: ...otifications".* FROM "notifications"  WHERE (uip  @> '{ip}')
                                                                ^
: SELECT "notifications".* FROM "notifications"  WHERE (uip  @> '{ip}')

And another attempt with:

Notification.where("uip  @> ?", ip)

Gives the error:

SELECT "notifications".* FROM "notifications"  WHERE (uip  @> 1)
PG::UndefinedFunction: ERROR:  operator does not exist: bigint[] @> integer
LINE 1: ...CT "notifications".* FROM "notifications"  WHERE (uip  @> 1)
                                                                  ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT "notifications".* FROM "notifications"  WHERE (uip  @> 1)
ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  operator does not exist: bigint[] @> integer
LINE 1: ...CT "notifications".* FROM "notifications"  WHERE (uip  @> 1)
                                                                  ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT "notifications".* FROM "notifications"  WHERE (uip  @> 1)

So how can I simply find objects by an integer variable inside a postgres array in rails?

Thanks!

Instead of Notification.where("uip @> ?", '{ip}') try Notification.where("uip @> ?", '{' + ip + '}') — Yevgeniy Anfilofyev
– Yevgeniy Anfilofyev, Commented Feb 10, 2015 at 12:36

sandeep · Accepted Answer · 2015-02-10 12:54:57Z

4

There are multiple ways you can query in a postgres array using ActiveRecord:

Fixing your query:

ip = 1    
Notification.where("uip @> '{?}'", ip)

Using 'ANY':

ip = 1
Notification.where("uip && ARRAY[?]", ip)

answered Feb 10, 2015 at 12:54

sandeep

5212 silver badges7 bronze badges

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

3 Comments

Laser Over a year ago

First one works thanks :), second doesn't seem to: PG::UndefinedFunction: ERROR: operator does not exist: bigint[] && integer[]

sandeep Over a year ago

I would suggest you to go with @> as you can index it with gin.

Kazik Over a year ago

First one doesn`t seems to work with string arrays, but second one does

pozs · Accepted Answer · 2015-02-11 08:47:35Z

2

You need to create an array from your input:

Notification.where("uip @> CAST(ARRAY[?] AS BIGINT[])", ip)
// or
Notification.where("uip @> CAST(? AS BIGINT[])", '{' + ip.to_s + '}')
// or
Notification.where("uip @> CAST('{' || ? || '}' AS BIGINT[])", ip)

If you want to test only one element, you can use the overlap (&&) operator too (which should be faster, a little). Or, you can use the ANY construct for arrays:

Notification.where("? = ANY (uip)", ip)

edited Feb 11, 2015 at 8:47

answered Feb 10, 2015 at 13:04

pozs

36.6k5 gold badges61 silver badges66 bronze badges

3 Comments

Laser Over a year ago

3rd and 4th work thanks :), the first two render errors: PG::UndefinedFunction: ERROR: operator does not exist: bigint[] @> integer[] and TypeError: no implicit conversion of Fixnum into String respectively

Laser Over a year ago

Would an ANY be faster than @> ?

pozs Over a year ago

@Laser then you need exeplicit casts everywhere; ANY is just simpler & works decent with smaller sets, but not indexable. The @> and && operators are indexable with gin.

Tom Gasaner · Accepted Answer · 2015-02-11 09:08:27Z

1

Try this one. I just moved your braces into condition:

ip = 1
Notification.where("uip  @> '{?}'", ip)

edited Feb 11, 2015 at 9:08

answered Feb 10, 2015 at 12:38

Tom Gasaner

15.8k10 gold badges64 silver badges74 bronze badges

1 Comment

Laser Over a year ago

PG::SyntaxError: ERROR: syntax error at or near "{"

Angu · Accepted Answer · 2015-02-11 09:49:28Z

0

Use it. i think this is helpful for you

ip = 1 
Notification.where("uip  @> ?", "#{ip.to_s.to_i}")

OR

Notification.where("uip @>?",ip.to_s.to_i)

edited Feb 11, 2015 at 9:49

answered Feb 10, 2015 at 12:37

Angu

8722 gold badges13 silver badges34 bronze badges

2 Comments

Laser Over a year ago

PG::InvalidTextRepresentation: ERROR: malformed array literal: "1"

Angu Over a year ago

Hi laser, I have changed my answer now.. You use this method.. I think it will be helpful for you

Collectives™ on Stack Overflow

How do you query postgres arrays in ruby on rails for integers using a variable?

4 Answers 4

3 Comments

3 Comments

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

3 Comments

1 Comment

2 Comments

Related