heroku SQL columnName problems

rubyonrails-talk
1

Hi all,

I've got a database of events, and (possibly foolishly) I've got column names such as eventDate, with capital letters. When I want to order the list of events by their date, I use the line:

@ed_ten_events = AllEvent.where(:city_location => 'Edinburgh', :ten_event => true).order('eventDate ASC')

This works fine on the local server, but on Heroku it crashes. The logs reveal that the Heroku Server is being searched with the wrong column name, one without capitals

ActionView::Template::Error (PGError: ERROR: column "eventdate" does not exist LINE 1: ..._events"."city_location" = 'Edinburgh') ORDER BY eventDate ...

Somewhere along the way eventDate is being changed to eventdate, and this is messing up the search. I've tested this using columns that don't contain capital letters and it works fine. So as far as I can tell, my database of events is still working.

Is there any way of forcing the Heroku 'order' search to keep the capitals?

Or do I need to change all my columns?

If I have to change columns, is there any way of keeping all the data?

Thanks in advance,

Mike

2

[...] Somewhere along the way eventDate is being changed to eventdate, and this is messing up the search. I've tested this using columns that don't contain capital letters and it works fine. So as far as I can tell, my database of events is still working.

Is there any way of forcing the Heroku 'order' search to keep the capitals?

Or do I need to change all my columns?

That is what I would suggest, stick to the Rails conventions and life will be much easier. So it should be event_date.

If I have to change columns, is there any way of keeping all the data?

Yes of course, just use rename_column in a migration. No doubt your automated tests are comprehensive so in terms of the code global search and replace should do most of the work for you.

Colin

3

Michael Baldock wrote in post #971769:

Hi all,

I've got a database of events, and (possibly foolishly) I've got column names such as eventDate, with capital letters.

Oy. I agree with Michael that this is probably worth changing.

When I want to order the list of events by their date, I use the line:

@ed_ten_events = AllEvent.where(:city_location => 'Edinburgh', :ten_event => true).order('eventDate ASC')

This works fine on the local server,

Using what DB?

but on Heroku it crashes. The logs reveal that the Heroku Server is being searched with the wrong column name, one without capitals

Right. Heroku uses PostgreSQL, which converts all identifiers to lowercase unless you surround them with double quotes. Check the Postgres docs for more on this.

Best,

4

Cheers for the help guys, I'd gotten used to writing variables with capital letters like that, and so resisted the rails convention to my peril. I'll change all the names

@Colin "Yes of course, just use rename_column in a migration."

Yeah that seems pretty obvious now you say it, cheers.

@Marnen "Heroku uses PostgreSQL, which converts all identifiers to lowercase"

I'd read Heorku was PostgreSQL, but didn't know it did this, thanks for the help.

5

Try to use Xeround free SQL database on Heroku, if you want, send a message to kopper8@gmail.com to get in invite