Heroku + Rails + PostgreSQL Issue

Question

I have put up an app on Heroku that is running fine locally (using sqlite3 as the gem for the database in testing), and when I push to Heroku and try to run it I keep getting this error:

 ActiveRecord::StatementInvalid (PG::Error: ERROR:  syntax error at or near "order"
LINE 1: ...lery_url_id = 'k19fv2mytjEb_3gCezLeRA') ORDER BY `order` ASC                                                              ^
: SELECT "pictures".* FROM "pictures"  WHERE (gallery_url_id = 'k19fv2mytjEb_3gCezLeRA') ORDER BY `order` ASC):
app/controllers/galleries_controller.rb:38:in `show'

Specifically on this line:

@pictures = Picture.find(:all, :conditions => [ 'gallery_url_id = ?', @gallery.url_id ], :order => "`order` ASC")

NOTE: order is a database field, not a SQL call or reference. So don't go telling me I am doing two orders. That would be silly. Unless somehow it is being parsed that way. Which would also be silly.

I understand that it is some issue from using SQLite in the local testing and PostgreSQL (pg) in the production environment. My question is what do I need to do to fix this? Is it due to a flag that I call in the find that is only supported by SQLite and not PostgreSQL?

Limited knowledge of PG here, but have you tried [order] instead of 'order' ? — Dominic Goulet
– Dominic Goulet, Commented Jul 13, 2012 at 17:34
Did that and the only thing that was modified was that it was now a syntax order dealing with the "[" so I am guessing that's a failed attempt :/ — user1470511
– user1470511, Commented Jul 13, 2012 at 17:39
@DominicGoulet: That's a SQL-Server-ism, double quotes are the standard syntax. — mu is too short
– mu is too short, Commented Jul 13, 2012 at 17:44

mu is too short · Accepted Answer · 2012-07-13 17:43:42Z

7

Backticks for quoting identifiers are a MySQL-ism that SQLite also supports. The standard syntax (which PostgreSQL uses) for quoting identifiers is to use double quotes:

:order => '"order" ASC'

I'd recommend that you do two things as soon as possible:

Change the name of your "order" column to something that isn't reserved.
Install PostgreSQL and develop using PostgreSQL is you're planning on deploying to Heroku. Your development stack should always exactly match your deployment stack.

The second point is quite important. Database portability is a myth, there are so many little differences between databases that writing code that works the same in multiple databases is difficult and means writing your own portability layer (and no, ActiveRecord is not that portability layer). This minor quoting problem will probably be the first of many little issues.

answered Jul 13, 2012 at 17:43

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Emily Over a year ago

Going between SQLite and Postgres is particularly difficult, in my experience. SQLite has a lot of strange little quirks, especially with column typing, and Postgres is quite strict about types.

mu is too short Over a year ago

@Emily: Yeah, that's probably as bad as it gets: SQLite is super-loose, PostgreSQL is super-strict.

Collectives™ on Stack Overflow

Heroku + Rails + PostgreSQL Issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related