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?

Solution 1

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.

Solution 2

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)

Solution 3

Use Arel:

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

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

Solution 4

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');

Solution 5

There are several answers, none of which are very satisfactory.

• LOWER(bar) = LOWER(?) will work on MySQL and Postgres, but is likely to perform terribly on MySQL: MySQL won't use its indexes because of the LOWER function. On Postgres you can add a functional index (on LOWER(bar)) but MySQL doesn't support this.
• MySQL will (unless you have set a case-sensitive collation) do case-insensitive matching automatically, and use its indexes. (bar = ?).
• From your code outside the database, maintain bar and bar_lower fields, where bar_lower contains the result of lower(bar). (This may be possible using database triggers, also). (See a discussion of this solution on Drupal). This is clumsy but does at least run the same way on pretty much every database.

View more solutions

Comments

[Bill Karwin]

You can also make sure it's upper: WHERE UPPER(bar) = UPPER(?)

[Dan Rosenstark]

+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.

[troelskn]

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

[Christopher Maujean]

The whole point of AR/AM is to allow you to use different database back ends in development and production. In my opinion, the failing here is in how the queries are generated by AR/AM.

[MarkR]

@christopher Maujean: see joelonsoftware.com/articles/LeakyAbstractions.html for why this is a bad idea.

[Christopher Maujean]

@MarkR: That article says (to me) not that abstraction is a bad idea, but that because abstraction is inherently leaky, I should know how to use the things being abstracted first and how the abstraction itself does the abstracting. In the case of Rails, I should know how SQL differs between MySQL, SQLite, and Postgres (the 3 SQL based database backends that I use) and how AR/AM performs the abstraction, so that I can recognize potential leaks and competently deal with them.

[richo]

I'm not 100% sure, but my recollection is that this will not use any indexes that may be present on foo because it can't scan a function's return value against the index.

[mu is too short]

@Richo: But you can create an index in upper(bar) if you need it: postgresql.org/docs/current/interactive/sql-createindex.html

[M. Scott Ford]

I hope I'm not trolling here, but I have two objections to this response. First, this is not an answer to the question. Second, using a different database in development and production is not the primary issue. What's really wrong is that an abstraction was not used to perform a case insensitive query. Ideally, active record would provide this capability without having to reach down into vendor specific SQL. I suggestion is to take a peek at the suggestion found in stackoverflow.com/questions/2220423/…

[MarkR]

Ok, it didn't answer the question. You can, in principle, write a case-insensitive query in MySQL and PG (for some definition of case-insensitive; are the collations really identical?). However, that won't really solve the OP's problem, of making bug-free code when they are using a different DB in dev and production. Even if the case-sensitivity problems are solved, there will be some OTHER problems biting the OP on the arse later, which are avoided by not using different software stacks in dev and production.

[mtjhax]

@MarkR Of course your advice is perfectly sound, but OP's question is still valid. Writing queries to be SQL-compliant and platform-independent has some value, especially if there's any chance the system may need to be ported to another database in the future. Apparently there is no perfect answer in this case.

[scarver2]

I recommend the parameter passed to Arel be upper-cased in Ruby so that additional processing by SQL is not required. where("UPPER(bar) = ?", parameter.upcase)

[Martin T.]

Wish I could give more than +1 ... I never knew Arel could do this ! Maybe because it's almost completely undocumented ? Hm...

[Kadarach]

Yes --- IS this documented anywhere?

[ADTC]

Well thanks for point 2, I discovered that it is indeed case-insensitive by default.

[ADTC]

This is awesome! I can use | to have multiple search keywords.

[Halil Özgür]

For any nontrivial project, db abstraction is a very leaky one; which will cause problems, especially when leaned on too much. An exception might be when you're building an end-user ready-made solution which supports multiple db's as a feature. In that case you should always be on the alert and not let failing abstractions to leak into the codebase, have robust tests etc. Not worth it more often than not in my experience.

[aydow]

while regexps are extremely versatile, note that they are quite slow and you will notice this on larger data sets or slow servers