Ruby on Rails: Active Record query fails to execute only when hosted on heroku.

Question

I have a page which simply displays all of the Links in my database sorted by the voteCount. Here is the controller:

class PagesController < ApplicationController
  def index
    params[:per_page] ||= 5
    params[:page]     ||= 1

    @links = Link.order('voteCount DESC').paginate(:page => params[:page], :per_page => params[:per_page])

  end
end

I save query the database using the paginate plugin, and prepend it with:

.order('voteCount DESC')

When I run this command on my local server, it runs fine. However, as soon as I deploy it to heroku, it fails. This is the output I get when I check the logs/execute it in the console:

Link Load (2.0ms)  SELECT "links".* FROM "links" ORDER BY voteCount DESC LIMIT 5 OFFSET 0
ActiveRecord::StatementInvalid: PG::Error: ERROR:  column "votecount" does not exist
LINE 1: SELECT  "links".* FROM "links"  ORDER BY voteCount DESC LIMI...
                                                 ^

I've checked using the console, the voteCount column is definitely there. This might be due to the fact that my local environment runs sqlite3 and heroku makes me use postgres ....

Any help would be really appreciated. Thanks.

mu is too short · Accepted Answer · 2012-09-14T18:08:18.803

You have a case sensitivity problem. When you say something like:

create_table :links do |t|
  t.integer :voteCount
end

Rails will send SQL like this to PostgreSQL to create the column:

"voteCount" integer

The double quotes around the identifier make it case sensitive and that means that you have to refer to it as "voteCount" forevermore. If you say this:

.order('"voteCount" DESC')

everything will should work.

SQLite doesn't care about identifier case so you could say voteCount, votecount, VOTECOUNT, ... in SQLite and it wouldn't matter.

Rails always quotes the identifiers that it produces when talking to PostgreSQL. Normally this doesn't matter, the Rails convention is to use lower case identifiers and PostgreSQL folds unquoted identifiers to lower case before using them so everything works by default.

There are some relevant best practices here:

Develop and deploy on the same stack.
Use lower case column names with words separated by underscores with PostgreSQL. This also happens to match the usual Ruby conventions.

I'd recommend that you use vote_count as the column name instead.

Thanks a lot. I just fixed it. But i'm switching over to using postgresql for dev too. — Paul Benigeri, Sep 14 '12 at 15:53

Joey Hoang · Answer 2 · 2012-09-13T23:14:54.537

1

your heroku app probably doesn't have that column migrated yet

heroku run rake db:migrate

edited Sep 13 '12 at 23:14

answered Sep 13 '12 at 23:07

Joey Hoang

406
4
10

I feel like it might be a case sensitivity problem then. http://stackoverflow.com/questions/203399/how-do-you-write-a-case-insensitive-query-for-both-mysql-and-postgres There's a great answer by MarkR there that I'd like to reiterate here: don't use different software stacks for different environments. You'll get a lot of these errors – Joey Hoang Sep 13 '12 at 23:14
Identifiers in postgresql are case INsensitive, unless they are quoted. In the console output query you have, the "voteCount" isn't quoted. I'm confused. – kristianp Sep 14 '12 at 02:04

Ruby on Rails: Active Record query fails to execute only when hosted on heroku.

2 Answers2