How do you write a case insensitive query for both MySQL and Postgres?

mysql ruby-on-rails database postgresql heroku
DA. picture DA. · Oct 15, 2008 · Viewed 51.9k times · Source

I'm running a MySQL database locally for development, but deploying to Heroku which uses Postgres. Heroku handles almost everything, but my case-insensitive Like statements become case sensitive. I could use iLike statements, but my local MySQL database can't handle that.

What is the best way to write a case insensitive query that is compatible with both MySQL and Postgres? Or do I need to write separate Like and iLike statements depending on the DB my app is talking to?

Answer

MarkR picture MarkR · Oct 11, 2009

The moral of this story is: Don't use a different software stack for development and production. Never.

You'll just end up with bugs which you can't reproduce in dev; your testing will be worthless. Just don't do it.

Using a different database engine is out of the question - there will be FAR more cases where it behaves differently than just LIKE (also, have you checked the collations in use by the databases? Are they identical in EVERY CASE? If not, you can forget ORDER BY on varchar columns working the same)

Related questions

db:schema:load vs db:migrate with capistrano

I have a rails app that I'm moving to another server and I figure I should use db:schema:load to create the mysql database because it's recommended. My problem is that I'm using capistrano to deploy and it seems …

Read More
Error installing mysql2: Failed to build gem native extension

I am having some problems when trying to install mysql2 gem for Rails. When I try to install it by running bundle install or gem install mysql2 it gives me the following error: Error installing mysql2: ERROR: Failed to build …

Read More
Change a column type from Date to DateTime during ROR migration

I need to change my column type from date to datetime for an app I am making. I don't care about the data as its still being developed. How can I do this?

Read More