How to make case insensitive case in Rails with postgresql

Question

In the process of switching my development environment from sqlite3 to postgresql 8.4 and the last hurdle.

In my original, I had the following line in a helper method:

result = Users.find(:all, :order => "name collate NOCASE") 

which provided a very good case insensitive search. I can not reproduce this for postgresql. It should be easy - any ideas?

Thank.

Answer 1

 result = Users.find(:all, :order => "LOWER(name)") 

A bit from Brad and Frank.

Answer 2

LanecH's answer is adapted for Rails 3+ (including Rails 4 and 5):

 users = User.order('LOWER(name)') 

Or create a named scope that you can reuse:

 class User < ActiveRecord::Base scope :order_by_name, -> { order('LOWER(name)') } end users = User.order_by_name 

Answer 3

Now with Rails 5.2 you are likely to get a warning if you use the accepted answer.

REMEDY WARNING: A dangerous query method (a method whose arguments are used as raw SQL) called with non-attribute argument (s): "LOWER (?) ASC".

An alternative would be to use Arel (now it is merged with Rails):

 results = User.order(User.arel_table['name'].lower.asc) # results.to_sql == "SELECT \"users\".* FROM \"users\" ORDER BY LOWER(\"users\".\"name\") ASC" 

Answer 4

Have you considered saving your column as a citext type? It really just assimilates the lower () call, as I understand it. That would be automatic for you later. If there are times when you need a case sensitive search, this might not be the best idea.

Answer 5

In SQL, you can use ORDER BY LOWER (column name), don't know how to do it in Ruby. A functional index (also on LOWER (column name)) will help speed up the process.