Postgres case insensitive searching with Rails

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开发者_StackOverflow中文版 Rails standardize these things? What methods should I use to solve that?

Here is how to fix it with raw SQL

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

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

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.