Rails + postgres case insensitive searches

Rails
1

Hello all

I am wondering how rails handles case sensitivity in databases. If I
do a Person.find_all_by_name(“tim”) in mysql I would expect to get
tim, TIm, and Tim. Do I only get tim in postgres?

How do other people deal with this? Do you resort to find_by_sql for
all your postgres queries to get case insensitive results?

2

I usually just define the method myself.

def self.find_all_by_name(name)
find(:all, :conditions => [“LOWER(name) = ?”, name.downcase])
end

3

I guess that would work but it seems like a hassle to try and override
all the find_by methods. I wonder if it would be wise to override the
missing_method and intercept all find and find_by calls. I think I
remember the find_by methods are implemented via the missing method
intercept.

On the downside this means making a ton of function indexes in postgres.

4

On Thu, 2006-04-20 at 00:52 -0500, Shane S. wrote:

I usually just define the method myself.

def self.find_all_by_name(name)
find(:all, :conditions => [“LOWER(name) = ?”, name.downcase])
end

Or if you don’t mind using PostgreSQL keywords:

def self.find_all_by_name(name)
find(:all, :conditions => [“name ILIKE ?”, name])
end

Yours,

Tom
http://tomcopeland.blogs.com/

5

I was really hoping to avoid such a thing if at all possible, not only
because it breaks the database abstraction but also because it
probably won’t use an index.

6

Tim U. wrote:

end

Or if you don’t mind using PostgreSQL keywords:

def self.find_all_by_name(name)
find(:all, :conditions => [“name ILIKE ?”, name])
end

You can also use a regular expression search.

find( :all, :conditions => [ ‘name ~* ?’, name ] )

I’m pretty sure that by using a regex… it’ll use the index.

Robby

–
Robby R.
Founder & Executive Director

PLANET ARGON, LLC
Ruby on Rails Development, Consulting & Hosting

www.robbyonrails.com

+1 503 445 2457
+1 877 55 ARGON [toll free]
+1 815 642 4968 [fax]

7

I was really hoping to avoid such a thing if at all possible, not only
because it breaks the database abstraction but also because it
probably won’t use an index.

Check to see the actual query run from your development.log then pop
that
into psql with an “EXPLAIN” and see what postgres will do.

I’d be surprised if it didn’t use the index, unless the stats indicate
it
shouldn’t regardless…