CODEWITHSUNDEEP
ruby-on-rails-3postgresqlactiverecordruby-on-rails-3.2
RubyRedGrapefruit
RubyRedGrapefruit

Reputation: 12224

Is there a PostgreSQL setting that I can use to do case-insensitive queries on column values?

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)

Upvotes: 0

Views: 274

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324445

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.

Upvotes: 1

Related Questions