Rails + ActiveRecord + Postgres: How to match case insensitive substring?

Question

I have to look for all emails from the User model that have the substring "cpg". So it would match "[email protected]", "[email protected]", etc

I'm pretty sure how to do case insensitive (using User.where("lower(email)...") but I don't know how to find a substring.

I'm using Rails 3 + Postgres 9.x

Answer 1

Nothing in Rails to do it, you can just use PostgreSQL's ilike (case insensitive like function).

Like so: User.where("email ilike '%cpg%'")

Answer 2

If you don't want to have to always remember to call lower(email) (and input.downcase on your input in Ruby) whenever you search on your email field, you can make the email column itself case insensitive by using the citext data type.

That's what I just did. I created a migration like this:

class ChangeUsersEmailToCitext < ActiveRecord::Migration
  def up
    # Rails 4:
    #enable_extension("citext")
    # Rails 3:
    execute 'create extension citext'

    change_table :users do |t|
      t.change :email, :citext
    end
  end
end

And now I no longer have to do any extra effort to make the queries case insensitive! It's all handled automatically for me behind the scenes now.

This uses PostgreSQL's citext extension.

http://www.sfcgeorge.co.uk/posts/2013/11/12/case-insensitive-usernames-with-postgres has a nice article about this.