pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to order varchar data by word
7+ messages / 3 participants
[nested] [flat]
* How to order varchar data by word
@ 2009-10-14 14:13 Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-10-14 20:54 ` Re: How to order varchar data by word Adrian Klaver <aklaver@comcast.net>
0 siblings, 1 reply; 7+ messages in thread
From: Oliveiros C, @ 2009-10-14 14:13 UTC (permalink / raw)
To: pgsql-sql
Hello, list.
I have a table with a varchar field that I would like to order by word, not by ordinal, which seems to be the default on postgres.
Does anyone have a clue on how this can be done?
Many thanx in advance,
Best,
Oliveiros
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to order varchar data by word
2009-10-14 14:13 How to order varchar data by word Oliveiros C, <oliveiros.cristina@marktest.pt>
@ 2009-10-14 20:54 ` Adrian Klaver <aklaver@comcast.net>
2009-10-19 17:07 ` Re: How to order varchar data by word Oliveiros C, <oliveiros.cristina@marktest.pt>
0 siblings, 1 reply; 7+ messages in thread
From: Adrian Klaver @ 2009-10-14 20:54 UTC (permalink / raw)
To: pgsql-sql; +Cc: Oliveiros C, <oliveiros.cristina@marktest.pt>
On Wednesday 14 October 2009 7:13:22 am Oliveiros C, wrote:
> Hello, list.
>
> I have a table with a varchar field that I would like to order by word, not
> by ordinal, which seems to be the default on postgres.
>
> Does anyone have a clue on how this can be done?
>
> Many thanx in advance,
>
> Best,
> Oliveiros
Can you show the SQL you are using?
--
Adrian Klaver
aklaver@comcast.net
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to order varchar data by word
2009-10-14 14:13 How to order varchar data by word Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-10-14 20:54 ` Re: How to order varchar data by word Adrian Klaver <aklaver@comcast.net>
@ 2009-10-19 17:07 ` Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-10-19 17:32 ` Re: How to order varchar data by word Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Oliveiros C, @ 2009-10-19 17:07 UTC (permalink / raw)
To: Adrian Klaver <aklaver@comcast.net>; pgsql-sql
Hello, Adrian.
Thank you for your email.
I am not using any SQL at this time, but I 'd like to understand
what is exactly the criterion that the ORDER BY uses to order text.
Giving you some background :
I need to make an efficient join between data generated by my program
and data output by postgres and of course, it is mandatory that both lists
of records
be ordered using the same criterion.
I need to figure out what is exactly the criterion used by postgres so I can
mimic it on my
program. I thought it was ordinal, but it seems it isnt, as the @ symbol
comes before the 0 (zero).
According to ASCII numeric codes, it shouldn't
If any one can explain me exactly how the order by clause works on varchars,
I 'd really appreciate it.
I've already examined documentation carefully, but couldn't find it. Maybe I
looked on the wrong place...
Best,
Oliveiros
----- Original Message -----
From: "Adrian Klaver" <aklaver@comcast.net>
To: <pgsql-sql@postgresql.org>
Cc: "Oliveiros C," <oliveiros.cristina@marktest.pt>
Sent: Wednesday, October 14, 2009 9:54 PM
Subject: Re: [SQL] How to order varchar data by word
> On Wednesday 14 October 2009 7:13:22 am Oliveiros C, wrote:
>> Hello, list.
>>
>> I have a table with a varchar field that I would like to order by word,
>> not
>> by ordinal, which seems to be the default on postgres.
>>
>> Does anyone have a clue on how this can be done?
>>
>> Many thanx in advance,
>>
>> Best,
>> Oliveiros
>
> Can you show the SQL you are using?
>
> --
> Adrian Klaver
> aklaver@comcast.net
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to order varchar data by word
2009-10-14 14:13 How to order varchar data by word Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-10-14 20:54 ` Re: How to order varchar data by word Adrian Klaver <aklaver@comcast.net>
2009-10-19 17:07 ` Re: How to order varchar data by word Oliveiros C, <oliveiros.cristina@marktest.pt>
@ 2009-10-19 17:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-10-20 13:39 ` Re: How to order varchar data by word Oliveiros C, <oliveiros.cristina@marktest.pt>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2009-10-19 17:32 UTC (permalink / raw)
To: Oliveiros C, <oliveiros.cristina@marktest.pt>; +Cc: Adrian Klaver <aklaver@comcast.net>; pgsql-sql
"Oliveiros C," <oliveiros.cristina@marktest.pt> writes:
> If any one can explain me exactly how the order by clause works on varchars,
> I 'd really appreciate it.
It sorts according to the sort order imposed by your locale setting
(see LC_COLLATE in particular).
A lot of locales have weird and wonderful rules that embody somebody's
notion of "dictionary order". If you don't like it, try switching to
C locale. Or you could learn enough about locale definitions to create
your own.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to order varchar data by word
2009-10-14 14:13 How to order varchar data by word Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-10-14 20:54 ` Re: How to order varchar data by word Adrian Klaver <aklaver@comcast.net>
2009-10-19 17:07 ` Re: How to order varchar data by word Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-10-19 17:32 ` Re: How to order varchar data by word Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-10-20 13:39 ` Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-10-20 13:46 ` Re: How to order varchar data by word Adrian Klaver <aklaver@comcast.net>
0 siblings, 1 reply; 7+ messages in thread
From: Oliveiros C, @ 2009-10-20 13:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Adrian Klaver <aklaver@comcast.net>; pgsql-sql
Hello, Tom.
Thank you for your e-mail
Even though I am not familiar with the exact procedure to switch to other
locales,
I'm gonna research this more deeply.
Anyway, I would appreciate if you could spare me some time and give me a
couple of pointers to this subject.
Again, thank you
Best,
Oliveiros
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Oliveiros C," <oliveiros.cristina@marktest.pt>
Cc: "Adrian Klaver" <aklaver@comcast.net>; <pgsql-sql@postgresql.org>
Sent: Monday, October 19, 2009 6:32 PM
Subject: Re: [SQL] How to order varchar data by word
> "Oliveiros C," <oliveiros.cristina@marktest.pt> writes:
>> If any one can explain me exactly how the order by clause works on
>> varchars,
>> I 'd really appreciate it.
>
> It sorts according to the sort order imposed by your locale setting
> (see LC_COLLATE in particular).
>
> A lot of locales have weird and wonderful rules that embody somebody's
> notion of "dictionary order". If you don't like it, try switching to
> C locale. Or you could learn enough about locale definitions to create
> your own.
>
> regards, tom lane
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to order varchar data by word
2009-10-14 14:13 How to order varchar data by word Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-10-14 20:54 ` Re: How to order varchar data by word Adrian Klaver <aklaver@comcast.net>
2009-10-19 17:07 ` Re: How to order varchar data by word Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-10-19 17:32 ` Re: How to order varchar data by word Tom Lane <tgl@sss.pgh.pa.us>
2009-10-20 13:39 ` Re: How to order varchar data by word Oliveiros C, <oliveiros.cristina@marktest.pt>
@ 2009-10-20 13:46 ` Adrian Klaver <aklaver@comcast.net>
2009-10-20 15:54 ` Re: How to order varchar data by word Oliveiros C, <oliveiros.cristina@marktest.pt>
0 siblings, 1 reply; 7+ messages in thread
From: Adrian Klaver @ 2009-10-20 13:46 UTC (permalink / raw)
To: Oliveiros C, <oliveiros.cristina@marktest.pt>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
On Tuesday 20 October 2009 6:39:23 am Oliveiros C, wrote:
> Hello, Tom.
>
> Thank you for your e-mail
>
> Even though I am not familiar with the exact procedure to switch to other
> locales,
> I'm gonna research this more deeply.
>
> Anyway, I would appreciate if you could spare me some time and give me a
> couple of pointers to this subject.
>
> Again, thank you
>
> Best,
> Oliveiros
>
A good place to start is:
http://www.postgresql.org/docs/8.4/interactive/locale.html
Check out the Further Reading section at:
http://www.postgresql.org/docs/8.4/interactive/multibyte.html#AEN30078
--
Adrian Klaver
aklaver@comcast.net
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to order varchar data by word
2009-10-14 14:13 How to order varchar data by word Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-10-14 20:54 ` Re: How to order varchar data by word Adrian Klaver <aklaver@comcast.net>
2009-10-19 17:07 ` Re: How to order varchar data by word Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-10-19 17:32 ` Re: How to order varchar data by word Tom Lane <tgl@sss.pgh.pa.us>
2009-10-20 13:39 ` Re: How to order varchar data by word Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-10-20 13:46 ` Re: How to order varchar data by word Adrian Klaver <aklaver@comcast.net>
@ 2009-10-20 15:54 ` Oliveiros C, <oliveiros.cristina@marktest.pt>
0 siblings, 0 replies; 7+ messages in thread
From: Oliveiros C, @ 2009-10-20 15:54 UTC (permalink / raw)
To: Adrian Klaver <aklaver@comcast.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
Hello, Adrian, thanks for the links.
I enjoyed the technique on the end of the second page (on the version 8.3
with comments), the shadow column trick.
Given that LC_COLLATE is immutable, unless a new database is created, I
guess I'll give the shadow column a try.
Best,
Oliveiros
----- Original Message -----
From: "Adrian Klaver" <aklaver@comcast.net>
To: "Oliveiros C," <oliveiros.cristina@marktest.pt>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>; <pgsql-sql@postgresql.org>
Sent: Tuesday, October 20, 2009 2:46 PM
Subject: Re: [SQL] How to order varchar data by word
> On Tuesday 20 October 2009 6:39:23 am Oliveiros C, wrote:
>> Hello, Tom.
>>
>> Thank you for your e-mail
>>
>> Even though I am not familiar with the exact procedure to switch to other
>> locales,
>> I'm gonna research this more deeply.
>>
>> Anyway, I would appreciate if you could spare me some time and give me a
>> couple of pointers to this subject.
>>
>> Again, thank you
>>
>> Best,
>> Oliveiros
>>
>
> A good place to start is:
> http://www.postgresql.org/docs/8.4/interactive/locale.html
> Check out the Further Reading section at:
> http://www.postgresql.org/docs/8.4/interactive/multibyte.html#AEN30078
>
> --
> Adrian Klaver
> aklaver@comcast.net
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2009-10-20 15:54 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-10-14 14:13 How to order varchar data by word Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-10-14 20:54 ` Adrian Klaver <aklaver@comcast.net>
2009-10-19 17:07 ` Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-10-19 17:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-10-20 13:39 ` Oliveiros C, <oliveiros.cristina@marktest.pt>
2009-10-20 13:46 ` Adrian Klaver <aklaver@comcast.net>
2009-10-20 15:54 ` Oliveiros C, <oliveiros.cristina@marktest.pt>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox