CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3postgresqlpg
Stephan1990
Stephan1990

Reputation: 475

Postgresql WHERE clause adds '' around?

This morning I switched to postgresql and now my where selects dont work anymore.

What I am trying to do is super simple:

shirt_ids = "1,5,6" # String generated by javascript
Shirt.where("id in (?)", shirt_ids)

This gives me :

PG::Error: ERROR:  invalid input syntax for integer: "1,5,6"
LINE 1: SELECT "shirts".* FROM "shirts"  WHERE (id in ('1,5,6'))

This works though:

Shirt.where("id in (#{shirt_ids})")

But as everybody knows is unsafe.

Im using:

pg (0.13.2 x86-mingw32)
rails (3.2.2)

Postgresql database is the newest version, I installed it this morning.

Thank you for your help. ^

Upvotes: 0

Views: 799

Answers (1)

MrTheWalrus
MrTheWalrus

Reputation: 9700

I believe Postgres is expecting an array, rather than a string for the IN function. If you convert your string to an array, it should work:

shirt_ids = "1,5,6"
Shirt.where('id in (?)', shirt_ids.split(','))

Also, you can do this, which looks a bit cleaner:

Shirt.where(:id => shirt_ids.split(','))

Upvotes: 6

Related Questions