Lists: | pgsql-general |
---|
From: | David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | case insensitive sorting & searching in oracle 10g |
Date: | 2004-08-05 09:04:20 |
Message-ID: | 4111F814.3020504@zara.6.isreserved.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
in oracle 10g, you can issue:
ALTER SESSION SET NLS_COMP = ansi;
ALTER SESSION SET NLS_SORT = binary_ci;
do you think this is an elegant solution for case insensitive sorting &
searching? is there interest in seeing this in postgres?
--
dave
From: | Pierre-Frédéric Caillaud <lists(at)boutiquenumerique(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: case insensitive sorting & searching in oracle 10g |
Date: | 2004-08-05 09:52:58 |
Message-ID: | opsb862kwbcq72hf@musicbox |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
create a functional index on lower case value of your column.
ORDER BY lower case value of your column.
> in oracle 10g, you can issue:
>
> ALTER SESSION SET NLS_COMP = ansi;
> ALTER SESSION SET NLS_SORT = binary_ci;
>
> do you think this is an elegant solution for case insensitive sorting &
> searching? is there interest in seeing this in postgres?
>
From: | David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)com> |
---|---|
To: | Pierre-Frédéric Caillaud <lists(at)boutiquenumerique(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: case insensitive sorting & searching in oracle 10g |
Date: | 2004-08-05 11:41:51 |
Message-ID: | 41121CFF.7010908@zara.6.isreserved.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
- not transparent
- can't automatically make all values fed to SELECT case-converted
- not transparent
Pierre-Frédéric Caillaud wrote:
>
> create a functional index on lower case value of your column.
> ORDER BY lower case value of your column.
>
>
>> in oracle 10g, you can issue:
>>
>> ALTER SESSION SET NLS_COMP = ansi;
>> ALTER SESSION SET NLS_SORT = binary_ci;
>>
>> do you think this is an elegant solution for case insensitive sorting
>> & searching? is there interest in seeing this in postgres?
--
dave
From: | Tommi Maekitalo <t(dot)maekitalo(at)epgmbh(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: case insensitive sorting & searching in oracle 10g |
Date: | 2004-08-05 13:34:00 |
Message-ID: | 200408051534.00196.t.maekitalo@epgmbh.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Hi,
I don't like the solution. "Select ... order by ..." should be self-contained
and not dependant of some settings. Case-insensitive sort should be specified
in the order-by-clause like "select ... order by lower(a)".
Tommi
Am Donnerstag, 5. August 2004 11:04 schrieb David Garamond:
> in oracle 10g, you can issue:
>
> ALTER SESSION SET NLS_COMP = ansi;
> ALTER SESSION SET NLS_SORT = binary_ci;
>
> do you think this is an elegant solution for case insensitive sorting &
> searching? is there interest in seeing this in postgres?
From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: case insensitive sorting & searching in oracle 10g |
Date: | 2004-08-05 14:32:36 |
Message-ID: | 20040805072939.F68582@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Thu, 5 Aug 2004, David Garamond wrote:
> in oracle 10g, you can issue:
>
> ALTER SESSION SET NLS_COMP = ansi;
> ALTER SESSION SET NLS_SORT = binary_ci;
>
> do you think this is an elegant solution for case insensitive sorting &
> searching? is there interest in seeing this in postgres?
IMHO, no on both questions. There's always danger on relying on the
value of session variables in general in that an application must either
set the variable immediately before sending queries that use it (breaking
the transparency) or must be willing to deal with the fact that it might
not be what you expect. For the second, I don't see how this really does
much that the standard spec collation stuff can't do better and I'd think
that'd be a much better route to go.
From: | David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)com> |
---|---|
To: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: case insensitive sorting & searching in oracle 10g |
Date: | 2004-08-05 16:39:02 |
Message-ID: | 411262A6.5050008@zara.6.isreserved.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Stephan Szabo wrote:
>>in oracle 10g, you can issue:
>>
>> ALTER SESSION SET NLS_COMP = ansi;
>> ALTER SESSION SET NLS_SORT = binary_ci;
>>
>>do you think this is an elegant solution for case insensitive sorting &
>>searching? is there interest in seeing this in postgres?
>
> IMHO, no on both questions. There's always danger on relying on the
> value of session variables in general in that an application must either
> set the variable immediately before sending queries that use it (breaking
> the transparency) or must be willing to deal with the fact that it might
> not be what you expect. For the second, I don't see how this really does
> much that the standard spec collation stuff can't do better and I'd think
> that'd be a much better route to go.
Could you point me where in the archives can I read more? I'm having a
bit of trouble finding discussion on this. Thanks.
--
dave
From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: case insensitive sorting & searching in oracle 10g |
Date: | 2004-08-05 17:05:44 |
Message-ID: | 20040805095204.M72010@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Thu, 5 Aug 2004, David Garamond wrote:
> Stephan Szabo wrote:
> >>in oracle 10g, you can issue:
> >>
> >> ALTER SESSION SET NLS_COMP = ansi;
> >> ALTER SESSION SET NLS_SORT = binary_ci;
> >>
> >>do you think this is an elegant solution for case insensitive sorting &
> >>searching? is there interest in seeing this in postgres?
> >
> > IMHO, no on both questions. There's always danger on relying on the
> > value of session variables in general in that an application must either
> > set the variable immediately before sending queries that use it (breaking
> > the transparency) or must be willing to deal with the fact that it might
> > not be what you expect. For the second, I don't see how this really does
> > much that the standard spec collation stuff can't do better and I'd think
> > that'd be a much better route to go.
>
> Could you point me where in the archives can I read more? I'm having a
> bit of trouble finding discussion on this. Thanks.
I didn't spend too much time looking, but there are a few that look like
they'll touch upon related issues:
http://archives.postgresql.org/pgsql-hackers/2003-11/msg01299.php
http://archives.postgresql.org/pgsql-hackers/2001-11/msg00610.php
http://archives.postgresql.org/pgsql-hackers/2002-11/msg00515.php
And a message where I pulled some text out of the SQL92 draft:
http://archives.postgresql.org/pgsql-general/2003-08/msg00620.php
From: | Pierre-Frédéric Caillaud <lists(at)boutiquenumerique(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: case insensitive sorting & searching in oracle 10g |
Date: | 2004-08-05 17:12:03 |
Message-ID: | opsb9redchcq72hf@musicbox |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
> IMHO, no on both questions. There's always danger on relying on the
> value of session variables in general in that an application must either
And what if you use a connection sharing/pooling software ? What happens
with the session vars ?
From: | David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)com> |
---|---|
To: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: case insensitive sorting & searching in oracle 10g |
Date: | 2004-08-06 10:03:40 |
Message-ID: | 4113577C.6050802@zara.6.isreserved.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Stephan Szabo wrote:
>>Could you point me where in the archives can I read more? I'm having a
>>bit of trouble finding discussion on this. Thanks.
>
> I didn't spend too much time looking, but there are a few that look like
> they'll touch upon related issues:
>
> http://archives.postgresql.org/pgsql-hackers/2003-11/msg01299.php
> http://archives.postgresql.org/pgsql-hackers/2001-11/msg00610.php
> http://archives.postgresql.org/pgsql-hackers/2002-11/msg00515.php
So, as I understand it, the current plan is:
1. charset + encoding will be tagged to each column (as per SQL standard)
2a. individual string values will be tagged with charset+encoding. this
incurs an overhead of 1-2 bytes per value.
or
2b. all string values will be stored in a single charset+encoding (e.g.
unicode + utf8). this will of course upset some people, e.g. japanese.
Is it 1+2a or 1+2b? Recent language implementations/VM like Parrot and
Ruby2 are inclined to 2a, I think.
--
dave
From: | David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)com> |
---|---|
To: | |
Cc: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: case insensitive sorting & searching in oracle 10g |
Date: | 2004-08-06 10:12:08 |
Message-ID: | 41135978.90200@zara.6.isreserved.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
David Garamond wrote:
> 2a. individual string values will be tagged with charset+encoding. this
> incurs an overhead of 1-2 bytes per value.
forgot to add: this overhead is just for "in-memory" or temporary value
(e.g. when being passed as arguments). in the storage itself, this is
not needed because charset+encoding is recorded in the column definition.
--
dave