Re: PostgreSQL makes me lie

We used to use FrontBase for our databases, but we have since
switched to PostgreSQL for
performance reasons. However, FrontBase did have very nice collation
support.

To get case insensitive searches (even on UTF-8 data), all you had to
do was alter the column
and set its collation to 'CASE_INSENSITIVE' like so:

alter column "test"."Column1" to collate
"INFORMATION_SCHEMA"."CASE_INSENSITIVE";

It would be VERY nice if PostgreSQL supported this as it would easily
allow you to write
case insensitive queries that use an index like:

select * from TEST where column1 like 'SOme ValUe%' or even just use
the equals operator
for an exact match yet still case insensitive.

Right now to get the same effect we create an index using a function
like 'lower(some_column)'.
But that requires us to write our queries like:

select * from test where lower(column1) like lower('SOme ValUe%');

Any ideas if better collation support is in the plans for future
versions of PostgreSQL?

Thanks,

____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 | brendan(at)clickspace(dot)com

ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB T2G 0V9

http://www.clickspace.com

On Apr 1, 2006, at 5:28 AM, Nikolay Samokhvalov wrote:

> I hate ILIKE. it's abnormal, as there is no way to forget it and use
> standard things (I can hardly wait collation support).
> So, every time I type 'ILIKE' I think: I DON'T LIKE ILIKE. In other
> words, 'ILIKE' couldn't be true for me and PostgreSQL makes me lie.
>
> --
> Best regards,
> Nikolay
> April, 1st. 2006
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org