CODEWITHSUNDEEP
ruby-on-railspostgresqlsqliteheroku
dpieri
dpieri

Reputation: 166

Rails and Heroku PGError: column does not exist

This page I have been developing for my app has been working fine locally (using sqllite3) but when I push it to Heroku, which 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 defiantly part of the Photo column. All my migrations are up to date, and when I pull the db back locally isUpForConsideration is still there, and the app still works locally.

I've 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\".neighborhood = 52) AND (isUpForCon...\n

Any idea what I could be doing wrong?

Upvotes: 4

Views: 3142

Answers (2)

IAmNaN
IAmNaN

Reputation: 10592

Another way to to get this error by modifying a migration and pushing changes up to Heroku without rebuilding the table.

Caveat: You'll lose data and perhaps references, so what I'm about to explain is a bad idea unless you're certain that no references will be lost. Rails provides ways to modify tables with migrations -- create new migrations to modify tables, don't modify the migrations themselves after they're created, generally.

Having said that, you can run heroku run rake db:rollback until that table you changed pops off, and then run heroku run rake db:migrate to put it back with your changes.

In addition, you can use the taps gem to back up and restore data. Pull down the database tables, munge them the way you need and then push the tables back up with taps. I do this quite often during the prototyping phase. I'd never do that with a live app though.

Upvotes: 1

Conspicuous Compiler
Conspicuous Compiler

Reputation: 6469

Your problem is that table and column names are case sensitive in PostgreSQL. This is normally hidden by automatically converting these to all-lowercase when making queries (hence why you see the error message reporting "isupforconsideration"), but if you managed to dodge that conversion on creation (by double quoting the name, like Rails does for you when you create a table), you'll see this weirdness. You need to enclose "isUpForConsideration" in double quotes when using it in a WHERE clause to fix this.

e.g.

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

Upvotes: 18

Related Questions