Re: String comparision in PostgreSQL

On 08/28/2012 10:46 PM, Nicola Cisternino wrote:

> 1) Why PostgreSQL don't use COLLATE to manage case sensitive /
> insensitive comparision (I think it's the best and ANSI standard way ....) ?

Support for per-column collations in PG was only added relatively
recently - in 9.1, by the looks:

http://www.postgresql.org/docs/9.1/static/collation.html

Prior to that, there was no meaningful way to use case insensitive
collations, as these would affect the whole database, including system
tables, which could break all sorts of things in new and exciting ways.

With the advent of per-column and per-operation collation control,
case-insensitive collations become very appealing.

One of the challenges with adding case insensitive collations is that,
AFAIK, collations are implemented using the operating system charset and
locale support, which may not offer case insensitive collation directly.

Another challenge is the rather ... variable ... meaning of "case
insensitive". Results are likely to vary between host platforms and
versions.

Still, now that per-col / per-op collation is supported, it'd be nice to
have. I don't know if it's just a matter of needing someone with the
desire and time (or funding) and expertise to design and build it, or if
there'd be issues with getting it accepted.

--
Craig Ringer