11

My development database is SQLite but I deploy my app to Heroku and they are using PostgreSQL.

Now sometimes I have two different results coming out if I perform searches because PostgreSQL is case-sensitive but SQLite is not.

Shouldn't Rails standardize these things? What methods should I use to solve that?

Here is how to fix it with raw SQL

MPelletier
  • 16,256
  • 15
  • 86
  • 137
antpaw
  • 15,444
  • 11
  • 59
  • 88
  • 1
    I'd recommend developing on top of PostgreSQL if you're deploying to Heroku. No ORM will protect you from database-specific behavior. – mu is too short Jul 31 '11 at 20:48
  • 1
    And I like using SQLite locally and Postgre on shipping, so I'm not tied to an certain DB. to each his own. – Tom Andersen Feb 09 '12 at 16:25

3 Answers3

42

Case insensitive searching in Postgres:

  • use ilike instead of like (case-insensitive like)
  • if you want to use =, make both sides either UPPER or LOWER
atrain
  • 9,139
  • 1
  • 36
  • 40
  • 4
    Thank you! The problem with `like` is that i don't know how to chain them with the `OR` operator. Is it save to use this with every supported db by rails? `"LOWER(title) LIKE LOWER('%#{search_term}%')"` – antpaw Jul 31 '11 at 20:40
  • 1
    well it doesnt brake my code in mysql, sqlite and pgsql so its good enough, thanks! – antpaw Aug 01 '11 at 17:56
  • use UPPER or LOWER if you want to run the same code on both MYSQL and POSTGRES (although that is obviously not best practice!) – Jan Hettich Jun 03 '13 at 05:20
4

There are many relatively common things that ActiveRecord doesn't handle, and LIKE matching is one of them. Here is how to achieve this using straight Arel instead.

Model.where(
  Model.arel_table[:title].matches("%#{search_term}%")
)

You can install Arel-Helpers to make this a little easier

Model.where(Model[:title].matches("%#{search_term}%"))

I previously recommended Squeel for this, but the original creator has stopped supporting it and there doesn't seem to be a full-time developer maintaining it. And since it plays around with private ActiveRecord APIs, it needs constant tending.

Adam Lassek
  • 35,156
  • 14
  • 91
  • 107
  • Great solution, and Squeel is awesome, but just a heads up that this is Postgres-specific behavior. If you are looking to do this for other RDBMS the output may be case sensitive. – kgx May 12 '15 at 18:43
  • @kgx actually this example will work as intended in both cases. As @antpaw says above, SQLite `LIKE` is case-insensitive by default. So this will use `LIKE` in SQLite and `ILIKE` in PostgreSQL. – Adam Lassek May 19 '15 at 23:13
4

Another DDL way of handling this is the citext data type, or case-insensitive TEXT.

Sean
  • 9,888
  • 4
  • 40
  • 43