views:

6318

answers:

7

I'm running a mySQL database locally for development, but deploying to Heroku which uses postgres. Heroku handles almost everything, except that my case insensitive Like statements become case sensitive. I know 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?

+10  A: 
select * from foo where upper(bar) = ?;

Be sure and set the parameter to upper case in the caller.

Paul Tomblin
You can also make sure it's upper: WHERE UPPER(bar) = UPPER(?)
Bill Karwin
+4  A: 

In postgres, you can do this:

SELECT whatever FROM mytable WHERE something ILIKE 'match this';

I'm not sure if there is an equivalent for MySQL but you can always do this which is a bit ugly but should work in both MySQL and postgres:

SELECT whatever FROM mytable WHERE UPPER(something) = UPPER('match this');
Adam Pierce
A: 

Converting to upper is best as it covers compatible syntax for the 3 most-used Rails database backends. PostgreSQL, MySQL and SQLite all support this syntax. It has the (minor) drawback that you have to uppercase your search string in your application or in your conditions string, making it a bit uglier, but I think the compatibility you gain makes it worthwile.

Both MySQL and SQLite3 have a case-insensitive LIKE operator. Only PostgreSQL has a case-sensitive LIKE operator and a PostgreSQL-specific (per the manual) ILIKE operator for case-insensitive searches. You might specify ILIKE insead of LIKE in your conditions on the Rails application, but be aware that the application will cease to work under MySQL or SQLite.

A third option might be to check which database engine you're using and modify the search string accordingly. This might be better done by hacking into / monkeypatching ActiveRecord's connection adapters and have the PostgreSQL adapter modify the query string to substitute "LIKE" for "ILIKE" prior to query execution. This solution is however the most convoluted and in light of easier ways like uppercasing both terms, I think this is not worh the effort (although you'd get plenty of brownie points for doing it this way).

I started working on a plugin to do something similar: http://github.com/myronmarston/case_insensitive_attributesThis isn't used in production anywhere, so don't run off and use it in your app, but it's a start.
Myron
A: 

You might also consider checking out the searchlogic plugin, which does the LIKE/ILIKE switch for you.

trevorturk
+10  A: 

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)

MarkR
+1 for the moral boost, thanks. Seriously, though, this is right and a lot better than any of the "just answer the question" answers. Though I hate these moral of the story answers in general, but in this case it's well done.
Yar
A: 

The UPPER() function definetly solve the problem !

Thank you !

Bill
+1  A: 

If you're using PostgreSQL 8.4 you can use the citext module to create case insensitive text fields.

MkV
Or add a functional index: http://www.postgresql.org/docs/7.3/static/indexes-functional.html
troelskn