Rails and Heroku PGError: column does not exist for where argument

Question

I've searched around and found some similar searches, but not one that actually detailed the issue I was having, so I figured I'd share my findings.

Problem
When pushing Ruby on Rails project to Heroku (DB = PostgresQL), I got some errors that I hadn't received in the dev environment (DB = SQLite).

Details
I have a model, say Message, that has a polymorphic association with an owner, which could either be a User or Customer (these aren't the actual models, so please don't ask why there is a polymorphic association when that obviously be something like a boolean customer column in a User model).

When querying the DB using ActiveRecord I had a method in the Message model that retrieved the messages associated with a certain User (it is important to note that in a polymorphic association for an owner, in this case, the Message model will add two columns, owner_id and owner_type, where owner_type would either be User or Customer)

The method was defined as follows:

    def self.getMessagesForUser(user)
      message_ids = "SELECT owner_id FROM messages WHERE owner_id = :user_id AND owner_type = \"User\""
      where("message_id IN (#{message_ids})", user_id: user.id)
    end

As you can see, the message_ids string is defined to be easily inserted into the where method for the model (hint: this is where the problem lies).

Error
I was receiving, the following error:

    ActionView::Template::Error (PGError: ERROR:  column "Customer" does not exist)

I knew something was awry because the argument was being perceived as a column, so I did some tinkering and found the solution.

Solution
I didn't realize that some querying languages (like SQL, PostgresQL, etc) are not as forgiving as SQLite when it comes to using either single-quotes or double-quotes in a query. Specifically, PostgresQL doesn't support double-quotes in the query, i.e. the User argument for owner_type in the message_ids string! I simply changed the line in the method from:

    message_ids = "SELECT owner_id FROM messages WHERE owner_id = :user_id AND owner_type = \"User\""

to:

    message_ids = "SELECT owner_id FROM messages WHERE owner_id = :user_id AND owner_type = \'User\'"

Put more simply:

"User" needed to be 'User'

And things went smoothly.

Downvoted for sharing knowledge - thanks for the support SO community. If you happen to be reading this now and plan on downvoting something, please include some explanation as to _why_ you think the question is "unworthy". As for this question, I thought it interesting that the error presented was confusing, i.e. the fact that it said the column "Customer" does not exist when "Customer" was not even being supplied as a column in the database, just as a value for the `owner_type`. I think knowing how to map strange errors to their root cause is always worthwhile to share. — MandM, Feb 21 '13 at 14:02
@MadM upvoted. Getting the same problem and this is helping me — Max Flex, Oct 20 '15 at 12:45

MandM · Accepted Answer · 2013-02-21T14:55:13.497

1

Solution
I didn't realize that some querying languages (like SQL, PostgresQL, etc) are not as forgiving as SQLite when it comes to using either single-quotes or double-quotes in a query. Specifically, PostgresQL doesn't support double-quotes in the query, i.e. the User argument for owner_type in the message_ids string! I simply changed the line in the method from:

    message_ids = "SELECT owner_id FROM messages WHERE owner_id = :user_id AND owner_type = \"User\""

to:

    message_ids = "SELECT owner_id FROM messages WHERE owner_id = :user_id AND owner_type = \'User\'"

Put more simply:

"User" needed to be 'User'

And things went smoothly.

edited Feb 21 '13 at 14:55

answered Feb 20 '13 at 18:49

MandM

3,293
4
34
56

Standard SQL doesn't like double quotes for strings. SQL uses single quotes for strings and double quotes to quote identifiers. MySQL uses backticks for identifiers and allows double quotes for strings, SQLite (AFAIK) allows any sort of quotes anywhere. Stop developing on top of SQLite if you're deploying to PostgreSQL, quoting is going to be the least of worries. Defaulting to SQLite was a foolish decision by the Rails people, you don't have to suffer for their lack of foresight. – mu is too short Feb 20 '13 at 19:23
@muistooshort, maybe this is covered in another question or something external to SO, but is there an explanation as to why SQLite is such an abomination? So far, this is the only issue that I've run into when porting from the development to production - this seems like personal opinion / or almost vendetta-ish (which I hope isn't the reason for the downvote). Either way, I'll be editing my answer to include some of what you've provided - thanks! – MandM Feb 21 '13 at 13:51
*Abomination* is a bit strong, SQLite is a great tool for the things. Some issues off the top of my head: loose type system (e.g. you can put strings in numeric columns without complaining), no date or time types and SQLite-specific string manipulation functions to fake them, non-standard quoting behavior, non-standard GROUP BY behavior. If you look for "worked in development, failed at Heroku" questions (which are legion) you'll see some of the problems. And any downvotes you saw were from someone else. – mu is too short Feb 21 '13 at 17:27

Rails and Heroku PGError: column does not exist for where argument

1 Answers1

Linked