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