Re: Mixed case text searches

(Pg 8.4)
Well I gave it a shot and for whatever reason it doesn't work for me. The table got created with accountname field of type citext. If I run a general select query the field correctly shows that it's of type citext. However, when I add my where clause accountname like 'a%', it returns no records. If I cap it ('A%') then I get the expected results. I'm using a BTree index on the field. Don't know if that makes a difference or not. Has anyone gotten this citext type to work? Is there an undocumented trick to getting it to work? Thanks

From: pgsql-novice-owner(at)postgresql(dot)org [mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Thom Brown
Sent: Tuesday, June 15, 2010 8:35 AM
To: Chris Campbell
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Mixed case text searches

On 15 June 2010 16:25, Chris Campbell <ccampbell(at)cascadeds(dot)com<mailto:ccampbell(at)cascadeds(dot)com>> wrote:
Hi list people. Okay I've read the documentation. Now it's time to talk to people that actually do this for a living. Mixed case searches, what is the best practice?
I'm searching for an account name: Acme Rockets Inc.

strSearchString = 'acme%'
Select * From Accounts Where AccountName = strSearchString
This will of course fail because the case doesn't match. So what is the best practice for performance?

I could use the Lower() function:
strSearchString = lower('acme%')
Select * From Accounts Where lower(AccountName) = strSearchString

Or I could use the ilike operator
strSearchString = 'acme%
Select * From Accounts Where AccountName ilike strSearchString

It's also been suggested that I keep a companion column that mirrors the account name column which is forced to lower case. This seems, well a bit desperate to me.

So, from a performance standpoint, what are people doing and why?

Many thanks for your replies.

Chris Campbell

You might want citext (case-insensitive text): http://www.postgresql.org/docs/8.4/static/citext.html

This means while the case is preserved when returning data, queries will match insensitively. Also indexes will work as expected on them. The alternative is to use lower() on every query and have a function index using lower().

Regards

Thom