64

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?

cHao
  • 84,970
  • 20
  • 145
  • 172
DA.
  • 930
  • 1
  • 7
  • 10
  • 4
    If you're using Postgres in prodution use Postgres locally too. This will not be the first issue you run into, and it also means you can't take advantage of anything Postgres-specific. – Samuel Neff Dec 16 '15 at 16:30
  • Maybe it was hard to install and run Postgres when you wrote this question but it's easy to run Postgres now using Docker – Andy Sep 21 '21 at 01:51

11 Answers11

73

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
  • 62,604
  • 14
  • 116
  • 151
  • 4
    +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. – Dan Rosenstark Jan 21 '10 at 01:05
  • 3
    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. – Christopher Maujean Sep 03 '11 at 23:32
  • @christopher Maujean: see http://www.joelonsoftware.com/articles/LeakyAbstractions.html for why this is a bad idea. – MarkR Sep 04 '11 at 20:32
  • @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. – Christopher Maujean Sep 08 '11 at 18:13
  • 3
    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 http://stackoverflow.com/questions/2220423/case-insensitive-search-in-rails-model – M. Scott Ford Dec 28 '11 at 02:39
  • 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. – MarkR Dec 28 '11 at 07:37
  • 6
    @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. – mtjhax Jan 11 '12 at 20:18
  • 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. – Halil Özgür Feb 13 '17 at 12:35
58
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.

Paul Tomblin
  • 179,021
  • 58
  • 319
  • 408
  • 11
    You can also make sure it's upper: WHERE UPPER(bar) = UPPER(?) – Bill Karwin Oct 15 '08 at 02:25
  • 2
    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. – richo Oct 17 '11 at 00:35
  • 5
    @Richo: But you can create an index in `upper(bar)` if you need it: http://www.postgresql.org/docs/current/interactive/sql-createindex.html – mu is too short Oct 17 '11 at 01:00
  • 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)` – scarver2 Oct 26 '12 at 22:48
36

Use Arel:

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

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

jswanner
  • 1,251
  • 11
  • 5
13

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
  • 33,531
  • 22
  • 69
  • 89
8

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.
tims
  • 83
  • 1
  • 4
  • Well thanks for point 2, I discovered that it is indeed case-insensitive by default. – ADTC Nov 28 '16 at 13:21
5

REGEXP is case insensitive (unless used with BINARY), and can be used, like so...

    SELECT id FROM person WHERE name REGEXP 'john';

...to match 'John', 'JOHN', 'john', etc.

Ben Wilhelm
  • 1,898
  • 3
  • 15
  • 12
  • This is awesome! I can use `|` to have multiple search keywords. – ADTC Nov 28 '16 at 13:25
  • while regexps are extremely versatile, note that they are quite slow and you will notice this on larger data sets or slow servers – aydow May 18 '17 at 04:45
2

use COLLATE.

http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

RuelB
  • 21
  • 1
2

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

MkV
  • 3,046
  • 22
  • 16
1

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

Trevor Turk
  • 465
  • 5
  • 10
1

You can also use ~* in postgres if you want to match a substring within a block. ~ matches case-sensitive substring, ~* case insensitive substring. Its a slow operation, but might I find it useful for searches.

Select * from table where column ~* 'UnEvEn TeXt';
Select * from table where column ~ 'Uneven text';

Both would hit on "Some Uneven text here" Only the former would hit on "Some UNEVEN TEXT here"

Sheldon Ross
  • 5,364
  • 7
  • 31
  • 37
0

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_attributes This isn't used in production anywhere, so don't run off and use it in your app, but it's a start. – Myron Marston Nov 17 '09 at 21:02