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@clickspace.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:

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

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:

2006-04-01 (占쏙옙), 19:26 -0700, Brendan Duddridge 占쏙옙占시깍옙:

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

I was on the postgres lists about 1 1/2 years ago, since then i haven't
been on then. when there was a question about modifying the way
postgres does collation and supports character sets on a database (as
opposed to table, or column-level manner) there was much clammer from
the developers about how that would break all past applications and it
was too difficult to program, but as i said, it's been a while. it
sounds like it's teh same. the developers are annoyed with constant
reminders of their flawed scheme and there is no timetable for a change
yet (that i know of).
qualifications noted.

joseph wrote:
...

I was on the postgres lists about 1 1/2 years ago, since then i haven't
been on then. when there was a question about modifying the way
postgres does collation and supports character sets on a database (as
opposed to table, or column-level manner) there was much clammer from
the developers about how that would break all past applications and it
was too difficult to program, but as i said, it's been a while. it
sounds like it's teh same. the developers are annoyed with constant
reminders of their flawed scheme and there is no timetable for a change
yet (that i know of).
qualifications noted.

You should read the archives for the 1 1/2 years you missed. Things
have greatly changed. Ah yes, the option with a custom datatype
with collation emulation was possible any time :-)

Kind regards
Tino

2006-04-01 (占쏙옙), 21:15 -0500, Robert Treat 占쏙옙占시깍옙:

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

Does this citext data type also provide case insensitive matching for
utf8 characters, as the gentleman's frontbase collation command did?

joseph wrote:

2006-04-01 (占쏙옙), 21:15 -0500, Robert Treat 占쏙옙占시깍옙:

On Saturday 01 April 2006 17:26, Brendan Duddridge wrote:

...

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

Does this citext data type also provide case insensitive matching for
utf8 characters, as the gentleman's frontbase collation command did?

Well, if you can read :-)
http://gborg.postgresql.org/project/citext/faq/faq.php?faq_id=105

Regards
Tino