Postgres case insensitive searching with Rails

Question

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

Answer 1

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

Answer 2

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

Answer 3

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.