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

Question

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 1

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)

Answer 2

select * from foo where upper(bar) = upper(?);

If you set the parameter to upper case in the caller, you can avoid the second function call.

Answer 3

Use Arel:

Author.where(Author.arel_table[:name].matches("%foo%"))

matches will use the ILIKE operator for Postgres, and LIKE for everything else.