Thread: PostgreSQL makes me lie
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
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:
> 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
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: > 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
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