Rails compare Time.current to database entry, Postgres vs. MySQL

Question

I've an issue with PostgreSQL using Rails. I have a table with datetime "from" and "to". In my controller I got the following:

@running = Model.where("'to' > ?", Time.now)
@finished = Model.where("'to' < ?", Time.now)

I already learned that I have to escape the "to", because it's a SQL statement. The problem now is that with MySQL and SQLite I can escape it with ` , so in my development environment (sqlite), it's working with:

.where("`to` > ?", Time.now)

And it actually works correct in the app.

But my production environment is Postgres, and Postgres doesn't support ``, and '' isn't working apparently because it doesn't matter what the entrys "to" is, it's always displaying the @running one even though it should be @finished.

What's wrong?

Thanks!!

Did you try using the table_name: Model.where("table_name.to > ?", Time.now)? — markets
– markets, Commented Nov 11, 2015 at 16:58
If i was you i would just change the column name and save yourself a load of headaches. — Max Williams
– Max Williams, Commented Nov 11, 2015 at 17:03
First, how is anything going to be greater than Time.now? Second, why not use Model.where(to: Time.now..5.years.ago) or whatever range you're actually trying to showcase? — Anthony
– Anthony, Commented Nov 11, 2015 at 17:12

mu is too short · Accepted Answer · 2015-11-11 17:35:56Z

This is a string literal in SQL:

'to'

This is a quoted identifier in (standard) SQL:

"to"

This is a quoted identifier in MySQL:

`to`

SQLite also supports the MySQL-specific syntax for compatibility reasons. And for completeness, this is a quoted identifier in SQL Server:

[to]

PostgreSQL follows the standard.

So when you say this:

Model.where("'to' > ?", Time.now)

you're trying to compare a constant string with a timestamp that is formatted as a string. That would be like this in Ruby:

'to' > '2015-11-11 09:27:11'

so of course it doesn't work as expected.

You have various options:

Rename the column so that you don't have to worry about quoting it.
Use the same database in your development, test, and production environments so that you don't have to worry about identifier quoting differences. This is a really good idea anyway, there are many differences between databases that ActiveRecord won't help you with.
Try to get ActiveRecord to write the SQL for you. This doesn't work that well for non-= comparisons unless you want to make a big confusing mess with AREL.

I would start with 2 because, as I said, there are many differences between databases (identifier quoting, reserved words, case sensitivity, date/time manipulation functions, SQL functionality, column types, GROUP BY behavior, ...) that ActiveRecord cannot help you with. Developing and testing on top of one database but deploying on another is just asking for trouble.

Collectives™ on Stack Overflow

Rails compare Time.current to database entry, Postgres vs. MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related