Rails 3.1 + postgresql database - sql syntax error for SELECT ... WHERE ... IN

Question

I have a Rails 3.1 setup with postgres 8.4. Here are my gem versions:

activerecord (3.1.3)
  activemodel (= 3.1.3)
  activesupport (= 3.1.3)
  arel (~> 2.2.1)
  tzinfo (~> 0.3.29)
activerecord-jdbc-adapter (1.2.1)
activerecord-jdbcpostgresql-adapter (1.2.1)
  activerecord-jdbc-adapter (~> 1.2.1)
  jdbc-postgres (~> 9.0.0)

Now, when I do this query in my controller:

@topics = Topic.find(:all, :conditions => ["\"ForumID\" in ?, @forum_ids]

I get this error:

ActiveRecord::JDBCError: ERROR: syntax error at or near "'abc123'"
Position: 62: SELECT "topic".* FROM "topic"  WHERE ("ForumID" in 'abc123','1234')
Completed 500 Internal Server Error in 314ms

ActiveRecord::StatementInvalid (ActiveRecord::JDBCError: ERROR: syntax error at or near "'abc123'"
Position: 62: SELECT "topic".* FROM "topic"  WHERE ("ForumID" in 'abc123','1234')):

I think the problem is where the parenthesis is being placed in the SQL statement. It should be after in instead of being before "ForumID".

SELECT "topic".* FROM "topic" WHERE "ForumID" in ('abc123','1234') works perfectly, so is this a bug in the postgresql adapter, or am I doing something wrong in my query?

Thanks.

Radek Paviensky · Accepted Answer · 2011-12-09 15:59:24Z

4

It looks like that you are missing parentheses inside the query. So this one should work:

@topics = Topic.find(:all, :conditions => ["ForumID in (?)", @forum_ids])

As you are on Rails 3.1, it's better to use:

@topis = Topic.where("ForumID in (?)", @forum_ids)

answered Dec 9, 2011 at 15:59

Radek Paviensky

8,4962 gold badges33 silver badges14 bronze badges

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

Comments

David Sulc · Accepted Answer · 2011-12-09 16:03:45Z

0

Don't you mean to have

@topics = Topic.find(:all, :conditions => ["\"ForumID\" in ?", @forum_ids]

Notice the closing " in the conditions.

As a side note, you can do this in Rails 3 with

 @topics = Topic.where("\"ForumID\" in ?", @forum_ids)

And possibly even (check the syntax)

@topics = Topic.where(:ForumID => @forum_ids)

answered Dec 9, 2011 at 16:03

David Sulc

26k3 gold badges55 silver badges54 bronze badges

1 Comment

Richard Over a year ago

i did have the closing ". i didn't copy and paste, so i missed it when i was typing up the question. good catch though.

Collectives™ on Stack Overflow

Rails 3.1 + postgresql database - sql syntax error for SELECT ... WHERE ... IN

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related