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.

LanceH · Accepted Answer · 2010-06-06T12:49:54.657

86

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

To take a little bit from both Brad and Frank.

edited Jun 06 '10 at 12:49

answered Jun 06 '10 at 12:44

LanceH

1,726
13
20

Nice, that worked thanks. Pointed me in the right direction to learn a bit more about this for myself. Using UPPER(name) also works and in fact is what has ended up in my code - for no particular reason. – brad Jun 07 '10 at 01:45
2

A more extreme solution I've resorted to is to have an extra column tracking the original column. Using triggers on insert or update, modify that column. I've used this in the past a couple times, where I wanted to sort titles of something and didn't want leading words of "a, an, the" to count. I also stripped out all special characters. This resulted in "title", "a title", "!title", "Title" sorting next to each other rather than scattered about. It also made it possible to edit the sortable field so I could make "title V" come before "title IX". – LanceH Jun 07 '10 at 02:18
1

If you need this on a `default_scope`, use `default_scope order('LOWER(name) ASC')`. Took me a bit to figure out the specifics. – Edward Loveall Nov 22 '13 at 15:15
3

Careful with default scopes. Even though they seem like a pretty good idea, they will often bite you in the ass down the line. Building a specific scope like `by_name` or `alpha` and using that everywhere may not be quite as DRY, but much easier to change down the road. Just food for thought – mr rogers Jun 06 '14 at 06:44
1

This answer now is deprecated. See Mario's answer for what works these days (And Ben's refinement to that in the comments). – Dave Morse Dec 29 '20 at 18:42
1

Rails 5.2 needs Arel.sql... ```result = Users.all.order(Arel.sql("lower(lname) asc"))``` – vanboom Mar 09 '21 at 01:56

score 32 · Answer 2 · edited Sep 26 '18 at 19:11

32

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

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

Or create a named scope you can reuse:

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

users = User.order_by_name

edited Sep 26 '18 at 19:11

Shepmaster

388,571
95
1,107
1,366

answered Jun 21 '14 at 09:31

Thomas Klemm

10,678
1
51
54

If you've come here wondering what the syntax is for descending order after applying the "LOWER" clause, it's `order('LOWER(name) DESC')`. The above answer can also be written as `order('LOWER(name) ASC')`. – changingrainbows May 31 '23 at 17:57

score 28 · Answer 3 · answered May 01 '18 at 10:26

28

Now with Rails 5.2 you probably will get a warning if using the accepted answer.

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

An alternative could be relying on Arel (it's merged now into Rails):

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

answered May 01 '18 at 10:26

Mario Pérez Alarcón

3,468
2
27
38

11

Or just use results = User.order(Arel.sql('lower(name)') . If you know your SQL it's a bit easier to follow. – Ben Trewern May 28 '18 at 14:11
1

Thanks Mario, this works really well when combined with `distinct` where writing your own SQL causes all sorts of group_by and select problems. – Paul Watson Nov 19 '18 at 08:11
@BenTrewern missing that closing paren: `User.order(Arel.sql('lower(name)'))` – Paul Lassiter Oct 06 '22 at 00:42

score 6 · Answer 4 · answered Jun 06 '10 at 21:30

6

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.

answered Jun 06 '10 at 21:30

rfusca

7,435
2
30
34

Interesting. That's a new type to me. A bit of reading suggests that you need to do install the citext module separately to enable this feature in 8.4 (but will be built in to 9.0). This would affect the portability of my code so I'll leave it out for now. I don't need to do a case sensitive search at this time but I think I would like to keep the option open at this time so will leave this for now. Very useful to know it exists though so thanks! – brad Jun 07 '10 at 01:52

score 5 · Answer 5 · answered Jun 06 '10 at 09:57

5

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.

answered Jun 06 '10 at 09:57

Frank Heikens

117,544
24
142
135

How to do case-insensitive order in Rails with postgresql

5 Answers5

Linked