Lists: | pgsql-general |
---|
From: | "Nikolay Samokhvalov" <samokhvalov(at)gmail(dot)com> |
---|---|
To: | PostgreSQL-general <pgsql-general(at)postgresql(dot)org> |
Subject: | PostgreSQL makes me lie |
Date: | 2006-04-01 12:28:45 |
Message-ID: | e431ff4c0604010428s6ddd59c5hfa7a8f586711b110@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Brendan Duddridge <brendan(at)clickspace(dot)com> |
---|---|
To: | nikolay(at)samokhvalov(dot)com |
Cc: | PostgreSQL-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: PostgreSQL makes me lie |
Date: | 2006-04-01 22:26:42 |
Message-ID: | C0768B8B-D6F5-44EB-88EF-B1FF59CEEE56@clickspace.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)clickspace(dot)com
ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB T2G 0V9
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
From: | Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | Brendan Duddridge <brendan(at)clickspace(dot)com>, nikolay(at)samokhvalov(dot)com |
Subject: | Re: PostgreSQL makes me lie |
Date: | 2006-04-02 02:15:12 |
Message-ID: | 200604012115.12761.xzilla@users.sourceforge.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Brendan Duddridge <brendan(at)clickspace(dot)com> |
---|---|
To: | Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org, nikolay(at)samokhvalov(dot)com |
Subject: | Re: PostgreSQL makes me lie |
Date: | 2006-04-02 02:26:17 |
Message-ID: | 299649E3-2C85-4A2E-AB62-FEE824F528B5@clickspace.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)clickspace(dot)com
ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB T2G 0V9
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
>
From: | joseph <kmh496(at)kornet(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: PostgreSQL makes me lie |
Date: | 2006-04-02 06:20:29 |
Message-ID: | 1143958829.19009.4.camel@var.sirfsup.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)clickspace(dot)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.
From: | Tino Wildenhain <tino(at)wildenhain(dot)de> |
---|---|
To: | joseph <kmh496(at)kornet(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: PostgreSQL makes me lie |
Date: | 2006-04-02 08:11:06 |
Message-ID: | 442F871A.8070000@wildenhain.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | joseph <kmh496(at)kornet(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: PostgreSQL makes me lie |
Date: | 2006-04-02 10:46:43 |
Message-ID: | 1143974804.24379.3.camel@var.sirfsup.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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?
From: | Tino Wildenhain <tino(at)wildenhain(dot)de> |
---|---|
To: | joseph <kmh496(at)kornet(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: PostgreSQL makes me lie |
Date: | 2006-04-02 10:57:50 |
Message-ID: | 442FAE2E.5010908@wildenhain.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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