How to do case-insensitive order in Rails with postgresql

Question

I am in the process of switching my development environment from sqlite3 to postgresql 8.4 and have one 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 nice case-insensitive search. I can't replicate this for postgresql. Should be easy - any ideas?

Thanks.

Answer 1

IN SQL you could use ORDER BY LOWER(columnname), no idea how to do it in Ruby. A functional index (also on LOWER(columnname) ) will help to speed things up.

Answer 2

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

To take a little bit from both Brad and Frank.

Answer 3

Have you considered storing your column as citext type? It really just internalizes the call to lower() as I understand it. It would be automatic for you afterwards. If there are times you need a case sensitive search, this may not be the best idea though.