From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-general@postgresql.org
Subject: Re: Case Sensitivity
Date: Thu, 13 Jan 2011 11:18:02 -0500
Message-ID: <20110113161801.GZ26731@shinkuro.com> (raw)
In-Reply-To: <D869A69BDB32184981F31AB9349C8C9B188D22@exch-be34.exchange.local>
References: <D869A69BDB32184981F31AB9349C8C9B188D1E@exch-be34.exchange.local>
<AANLkTi=mk9n3Szka_hy-wc8r8iBM-mHmQRmo9WPQ-hd1@mail.gmail.com>
<20110113152528.218550@gmx.net>
<D869A69BDB32184981F31AB9349C8C9B188D22@exch-be34.exchange.local>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: ajs@crankycanuck.ca
Subject: Re: Case Sensitivity
In-Reply-To: <20110113161801.GZ26731@shinkuro.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox