Rails and Heroku PGError: column does not exist

Question

This page that I developed for my application works fine locally (using sqllite3), but when I click on Heroku that uses PostgreSQL, I get this error:

NeighborhoodsController # (ActionView :: Template :: Error) "PGError: ERROR: column \" isupforconsideration \ "does not exist \ nLINE 1: ... \" photos \ "WHERE (neighborhood = 52 AND isUpForCon ... \ n

From this line of code:

@photos = Photo.where(["neighborhood = ? AND isUpForConsideration = ?", @neighborhood.id, 1]) 

isUpForConsideration is the calling part of the Photo column. All my migrations are updated, and when I pull out the db locally, isUpForConsideration still exists and the application is still running locally.

I also tried:

 @photos = @neighborhood.photos(:conditions => {:isUpForConsideration => 1}) 

and

 @photos = @neighborhood.photos.where(["isUpForConsideration = 1"]) 

Which gives me this error:

NeighborhoodsController # (ActionView :: Template :: Error) "PGError: ERROR: column \" isupforconsideration \ "does not exist \ nLINE 1: ... tos \" WHERE (\ "photos \". = 52) AND (isUpForCon .. . \ n

Any idea what I can do wrong?

Answer 1

Your problem is that table and column names are case sensitive in PostgreSQL . Usually this is hidden by automatically converting them to all lowercase letters when executing queries (therefore, why you see the error message "isupforconsideration"), but if you managed to avoid this conversion when creating (by double quoting the name, for example Rails, for when you create the table ), you will see this strangeness. You must enclose "isUpForConsideration" in double quotation marks when using it in the WHERE clause to fix this.

eg.

 @photos = @neighborhood.photos.where(["\"isUpForConsideration\" = 1"]) 

Answer 2

Another way to get this error is by changing the migration and pushing the changes to Heroku without rebuilding the table.

Caution: you will lose data and possibly links, so I'm going to explain this is a bad idea if you are not sure that the links will not be lost. Rails provides ways to modify tables using migration - create new migrations to modify tables, do not modify the migrations themselves after they are created as a whole.

Having said that, you can run heroku run rake db:rollback until this table changes, and then run heroku run rake db:migrate to return it with your changes.

In addition, you can use the tap label to backup and restore data. Extend the database tables, lift them up as you need, and then push the tables with taps. I do this quite often during the prototyping phase. I would never do this with a live application, though.