CODEWITHSUNDEEP
sqlruby-on-railspostgresqlheroku
Dan
Dan

Reputation: 3

Migration from SQLlite3 to PostgreSQL issue (Deployed to Heroku)

I've been running into some issues deploying my rails code to Heroku since my development environment was set to sqllite3 by default. This is the following log snapshot:

ActiveRecord::StatementInvalid (PG::Error: ERROR:  syntax error at or near ":"
2012-05-25T14:11:55+00:00 app[web.1]: 
2012-05-25T14:11:55+00:00 app[web.1]: LINE 1: ...LECT "records".* FROM "records"  WHERE (TIMESTAMP(:order) >=...
2012-05-25T14:11:55+00:00 app[web.1]: : SELECT "records".* FROM "records"  WHERE (TIMESTAMP(:order) >= '2011-05-25 00:00:00' and TIMESTAMP(:order) <= '2012-05-25 23:59:59') ORDER BY created_at):
2012-05-25T14:11:55+00:00 app[web.1]:   app/controllers/records_controller.rb:20:in `index'

The code fragment from my controller file:

opts = {:order => "created_at"}
opts[:conditions] = (@start_date.nil? ? "" : "TIMESTAMP(created_at::text) >= '#{@start_date.to_s(:db)}'")
opts[:conditions] += ((@start_date.nil? || @end_date.nil?) ? "" : " and ")
opts[:conditions] += (@end_date.nil? ? "" : "TIMESTAMP(created_at::text) <= '#{@end_date.to_s(:db)}'")

Originally, I had a "DATETIME" where you have TIMESTAMP so I switched it and still getting issues. The context of this code is that I'm running a query between dates with the following format: 2011-05-25 00:00:00

Upvotes: 0

Views: 133

Answers (1)

mu is too short
mu is too short

Reputation: 434665

Your created_at should already be a timestamp so there's no need for TIMESTAMP(created_at::text) to try and cast it to a timestamp; timestamp(x) won't work in PostgreSQL anyway. You shouldn't need that sort of thing for SQLite either.

You end up running SQL like this:

SELECT "records".*
FROM "records"
WHERE TIMESTAMP(:order) >= '2011-05-25 00:00:00'
  and TIMESTAMP(:order) <= '2012-05-25 23:59:59'
ORDER BY created_at

You're doing something somewhere that changes created_at::text to :order in the SQL. I haven't been able to reproduce that behavior but I suspect that you're running afoul of named placeholders (i.e. column = :value rather than column = ?) but that's just a wild guess.

In any case, if get rid of the timestamp(...) stuff and switch over to chaining your where calls, you'll get something much nicer:

query = Record.order(:created_at)
query = query.where('created_at >= ?', @start_date) if(@start_date.present?)
query = query.where('created_at <= ?', @end_date)   if(@end_date.present?)

Then you can query.all or query.paginate(...) or whatever to get your final results. That is (IMHO) much prettier than a pile of string wrangling and it should work the same anywhere.

If you're going to be deploying on top of PostgreSQL (i.e. Heroku) then you should develop and test on top of PostgreSQL. SQLite has some strange ideas about what SQL is and how its type system works, PostgreSQL is (thankfully) much less forgiving. You should always develop and deploy on the same stack and same applies all the way down to version numbers.

Upvotes: 1

Related Questions