Excellent. I've just downloaded citext and I'll try it out. Are there
any problems with it?
It seems like this should just be added as a default data type to
PostgreSQL.
Thanks,
____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com
ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB T2G 0V9
http://www.clickspace.com
On Apr 1, 2006, at 7:15 PM, Robert Treat wrote:
> On Saturday 01 April 2006 17:26, Brendan Duddridge wrote:
>> 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?
>>
>
> Improved collation support is being worked on but it's a complex
> problem so
> there's no realt ETA. (Developers interested in helping out our
> encouraged to
> send a note to -hackers). In the mean time check out the citext
> project:
> http://gborg.postgresql.org/project/citext/projdisplay.php
>
> --
> Robert Treat
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>