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

Question

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)

Looks like this might be pretty much the same question: stackoverflow.com/questions/2220423/… The answer seems fairly unsatisfying, but probably correct. Sorry. — Jason Swett
– Jason Swett, Commented Apr 10, 2013 at 21:36

Craig Ringer · Accepted Answer · 2013-04-10 23:27:50Z

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.

answered Apr 10, 2013 at 23:27

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RubyRedGrapefruit Over a year ago

You are the MAN. That was easy. Updating question with a quick how-to.

Collectives™ on Stack Overflow

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

Solved:

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Solved:

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related