Case-Insensitive Searches With PostgreSQL On Rails

Most Rails developers will have used one or both of MySQL and SQLite for development work. In both of these flavours of SQL, the “like” operator performs case-insensitive searches; this example from the MySQL documentation is a good illustration of how this work:

The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default. This means that if you search with col_name LIKE ‘a%’, you get all column values that start with A or a.

Let’s assume that you want this behaviour.

If, like a lot of Rails developers, you use Heroku for hosting or staging, you’ll run into a problem: Heroku uses PostgreSQL, and PostgreSQL’s “like” is case-sensitive. In practice, this means that you’ll test your code in development, deploy to Heroku, and see different results. How to fix this?

This fix is simple: PostgreSQL provides an “ilike” operator which performs case-insenstive searches (the behaviour for “ilike” is the same as for “like” in MySQL and SQLite). So, we can check which SQL adaptor Rails is using and use either “like” or “ilike” as appropriate. Here’s an example in an ActiveRecord scope:

scope :search, lambda { |phrase|  
  if connection.adapter_name == 'PostgreSQL'
    where("title ilike ?", "%#{phrase}%")
  else
    where("title like ?", "%#{phrase}%")
  end
}

This gives us the correct behaviour whatever flavour of SQL we’re using.

Note, by the way, that I’ve slightly simplified things here: “like” in MySQL and SQLite aren’t always case-insensitive, but they do act that way most of the time. Check the MySQL and SQLite documentation for more details.