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