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@xxxxxxxxxxxxxx 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
Attachment:
smime.p7s
Description: S/MIME cryptographic signature
