0

I have been using MySQL for 9 years and now have a new Rails 3.2 application in which I am using PostgreSQL. I am getting bitten by a couple of assumptions that I am bringing to the project.

With MySQL, I could set database settings so that this:

plan = Plan.find_by_name("platinum")

would return a record, even if the value in the db was Platinum. With the default settings of PostgreSQL, it doesn't work like that.

I'm sure this must be a common issue. I sure don't want my customers having to type in exact matches to case. I can't find anything online for some reason, I am just turning up 10-year old support cases on PostgreSQL's website in which this feature is not supported. Hopefully it has changed by now.

Solved:

Thanks to the answer below, here is how to accomplish this:

In Postgres 9.1+, the citext contrib module is shipped with the code. All you need to do is type this at the psql command line:

create extension citext

Now you can create a table with the citext column type:

create table people (name ctext);

Insert some rows to test:

insert into people (name) values ('ADRIAN');
insert into people (name) values ('adrian');

And test:

select * from people where name = 'Adrian';

Results:

  name  
--------
 adrian
 ADRIAN
(2 rows)
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
RubyRedGrapefruit
  • 12,066
  • 16
  • 92
  • 193
  • 1
    Looks like this might be pretty much the same question: http://stackoverflow.com/questions/2220423/case-insensitive-search-in-rails-model/2220595#2220595 The answer seems fairly unsatisfying, but probably correct. Sorry. – Jason Swett Apr 10 '13 at 21:36

1 Answers1

1

You can define the column as citext with the citext contrib module.

Another option is to use ilike with no wildcards. This will still use a b-tree index if one is present, at least a text_pattern_ops index if not a default btree. You'll need to escape the LIKE expression metacharacters documented in pattern matching to prevent accidental wildcard searches.

Personally I'm really hoping to see a COLLATE option for case insensitivity added now that per-expression COLLATE is supported... but right now, that's hand-waving future stuff.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778