Date: Thu, 13 Jan 2011 11:18:02 -0500
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-general@postgresql.org
Subject: Re: Case Sensitivity
Message-ID: <20110113161801.GZ26731@shinkuro.com>
Mail-Followup-To: Andrew Sullivan <ajs@crankycanuck.ca>,
	pgsql-general@postgresql.org
References: <D869A69BDB32184981F31AB9349C8C9B188D1E@exch-be34.exchange.local>
	<AANLkTi=mk9n3Szka_hy-wc8r8iBM-mHmQRmo9WPQ-hd1@mail.gmail.com>
	<20110113152528.218550@gmx.net>
	<D869A69BDB32184981F31AB9349C8C9B188D22@exch-be34.exchange.local>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <D869A69BDB32184981F31AB9349C8C9B188D22@exch-be34.exchange.local>
User-Agent: Mutt/1.5.18 (2008-05-17)

On Thu, Jan 13, 2011 at 03:29:03PM -0000, Phillip Smith wrote:

> modifying user inputted data is not an option for me. I need to
> maintain the original data. I had read about indexing on a lower
> function. This all seems a bit of a bodgy workaround to me. A clean
> Case Insensitive collation setting is cleaner. Is this supported in
> postgreSQL 9?

There isn't such a collation setting as far as I know, and it's
actually not as clean as you think.  It turns out to work reasonably
well in ASCII-land, but not very well in other circumstances.  In
general, the case-insensitive but case-preserving technique that
English-speaking computer users have come to find normal is a bad fit
for a wide variety of languages.  (Even "Latin" characterset-using
languages have trouble, because of historic ways of handling accents.
Is the capital version of é E or É?  Well, both, it turns out,
depending on whom you believe.  I could bore you about the effects of
this in the DNS all day long, but I'll resist the temptation.)

Putting an index on lower(column) and then doing all your comparisons
with lower(datum) works fairly well, and I don't see how it's any more
bodgy than a database-wide case insensitive collation.  For instance,
I can assure you that customers named Leblanc and LeBlanc care about
whether those two compare equally.  In your customer name field, if
you have a database-wide collation setting, you can't make the
distinction.

A

-- 
Andrew Sullivan
ajs@crankycanuck.ca