Re: Case-Insensitve Text Comparison

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "David E(dot) Wheeler" <david(at)kineticode(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Case-Insensitve Text Comparison
Date: 2008-06-02 04:08:35
Message-ID: 2335.1212379715@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> I really need case-insensitive string comparison in my database.

Okay ... according to whose locale?

> Ideally there'd be a nice ITEXT data type (and friends, ichar,
> ivarchar, etc.). But of course there isn't, and for years I've just
> used LOWER() on indexes and queries to get the same result.

> Only it turns out that I'm of course not getting the same result.

I think that means you're not using the right locale.

> 1. Does the use of the tolower() C function in the citext data type on
> pgfoundry basically give me the same results as using lower() in my
> SQL has for all these years?

[ broken record... ] Kinda depends on your locale. However, tolower()
is 100% guaranteed not to work for multibyte encodings, so citext is
quite useless if you're using UTF8. This is fixable, no doubt, but
it's not fixed in the project as it stands.

> 2. Isn't the ICU library distributed with PostgreSQL?

Nope, it is not, and we have already pretty much determined that we
do not want to make Postgres depend on ICU. See the archives.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Zdenek Kotala 2008-06-02 04:13:58 Re: Case-Insensitve Text Comparison
Previous Message David E. Wheeler 2008-06-02 03:53:25 Case-Insensitve Text Comparison