pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOdd sort behaviour
6+ messages / 4 participants
[nested] [flat]
* Odd sort behaviour
@ 2009-09-01 21:01 Rob Sargent <robjsargent@gmail.com>
2009-09-01 21:38 ` Re: Odd sort behaviour Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Rob Sargent @ 2009-09-01 21:01 UTC (permalink / raw)
To: pgsql-sql
Since when does "." sort as "nothing at all"
This select
select
distinct u.user_name
from
subscriber_user u,
subscription s,
subscription_template t
where
u.id = s.subscriber_entity_id
and s.template_id = t.id
and t.application_id = (select id from application where
short_name ='books')
order by u.user_name
\p\g
over this table def.(for user_name)
\d subscriber_user
Table "public.subscriber_user"
+-----------------------+---------+---------------+
| Column | Type | Modifiers |
+-----------------------+---------+---------------+
| id | bigint | not null |
| last_name | text | |
| first_name | text | |
| user_name | text | |
| email_address | text | |
| force_password_change | boolean | default false |
| title | text | |
+-----------------------+---------+---------------+
Indexes:
"subscriber_user_pkey" PRIMARY KEY, btree (id)
"idx__subscriber_users__lower_email_address" UNIQUE, btree
(lower(email_address))
"idx__subscriber_users__lower_user_name" UNIQUE, btree
(lower(user_name))
Foreign-key constraints:
"subscriber_user_id_fkey" FOREIGN KEY (id) REFERENCES subscriber(id)
is producing this sorted??? output
| adrianohazim@hotmail.com |
| adx008@show.org.tw |
| aecheniq@mac.com |
| a.ecke70@gmx.de |
| aelefant@unina.it |
| aeo_tw@hotmail.com |
| a.fischedick@t-online.de |
| aflores3432@gmail.com |
| afried@advancedneurosurgeons.com |
| agave007@comcast.net |
| agelsinger@amirsys.com |
| agis1doc@yahoo.gr |
using this client
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
and this server
show server_version;
+----------------+
| server_version |
+----------------+
| 8.3.7 |
+----------------+
(1 row)
nsm=# show server_encoding
+-----------------+
| server_encoding |
+-----------------+
| UTF8 |
+-----------------+
(1 row)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Odd sort behaviour
2009-09-01 21:01 Odd sort behaviour Rob Sargent <robjsargent@gmail.com>
@ 2009-09-01 21:38 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-09-01 22:31 ` Re: Odd sort behaviour Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Scott Marlowe @ 2009-09-01 21:38 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-sql
On Tue, Sep 1, 2009 at 3:01 PM, Rob Sargent<robjsargent@gmail.com> wrote:
> Since when does "." sort as "nothing at all"
Since you set your locale equal to something like en_US instead of C
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Odd sort behaviour
2009-09-01 21:01 Odd sort behaviour Rob Sargent <robjsargent@gmail.com>
2009-09-01 21:38 ` Re: Odd sort behaviour Scott Marlowe <scott.marlowe@gmail.com>
@ 2009-09-01 22:31 ` Rob Sargent <robjsargent@gmail.com>
2009-09-01 23:04 ` Re: Odd sort behaviour Greg Stark <gsstark@mit.edu>
0 siblings, 1 reply; 6+ messages in thread
From: Rob Sargent @ 2009-09-01 22:31 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-sql
How many ways might one accidentally do that I wonder.
Scott Marlowe wrote:
> On Tue, Sep 1, 2009 at 3:01 PM, Rob Sargent<robjsargent@gmail.com> wrote:
>
>> Since when does "." sort as "nothing at all"
>>
>
> Since you set your locale equal to something like en_US instead of C
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Odd sort behaviour
2009-09-01 21:01 Odd sort behaviour Rob Sargent <robjsargent@gmail.com>
2009-09-01 21:38 ` Re: Odd sort behaviour Scott Marlowe <scott.marlowe@gmail.com>
2009-09-01 22:31 ` Re: Odd sort behaviour Rob Sargent <robjsargent@gmail.com>
@ 2009-09-01 23:04 ` Greg Stark <gsstark@mit.edu>
2009-09-01 23:22 ` Re: Odd sort behaviour Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Greg Stark @ 2009-09-01 23:04 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-sql
On Tue, Sep 1, 2009 at 11:31 PM, Rob Sargent<robjsargent@gmail.com> wrote:
> How many ways might one accidentally do that I wonder.
Well most operating system distributions ask you when you install them
what region you're in and use a collation for that region.
In 8.4 you can check what collation a database is set to use with \l
in psql. In 8.3 the entire "cluster" has a single collation which you
can see using "show lc_collate".
You can see how your system's collations work by running sort:
$ LC_ALL=c sort s
a.ecke70@gmx.de
a.fischedick@t-online.de
adrianohazim@hotmail.com
adx008@show.org.tw
aecheniq@mac.com
aelefant@unina.it
aeo_tw@hotmail.com
aflores3432@gmail.com
afried@advancedneurosurgeons.com
agave007@comcast.net
agelsinger@amirsys.com
agis1doc@yahoo.gr
$ LC_ALL=en_US sort s
adrianohazim@hotmail.com
adx008@show.org.tw
aecheniq@mac.com
a.ecke70@gmx.de
aelefant@unina.it
aeo_tw@hotmail.com
a.fischedick@t-online.de
aflores3432@gmail.com
afried@advancedneurosurgeons.com
agave007@comcast.net
agelsinger@amirsys.com
agis1doc@yahoo.gr
--
greg
http://mit.edu/~gsstark/resume.pdf
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Odd sort behaviour
2009-09-01 21:01 Odd sort behaviour Rob Sargent <robjsargent@gmail.com>
2009-09-01 21:38 ` Re: Odd sort behaviour Scott Marlowe <scott.marlowe@gmail.com>
2009-09-01 22:31 ` Re: Odd sort behaviour Rob Sargent <robjsargent@gmail.com>
2009-09-01 23:04 ` Re: Odd sort behaviour Greg Stark <gsstark@mit.edu>
@ 2009-09-01 23:22 ` Rob Sargent <robjsargent@gmail.com>
2009-09-03 00:50 ` Re: Odd sort behaviour Lew <noone@lwsc.ehost-services.com>
0 siblings, 1 reply; 6+ messages in thread
From: Rob Sargent @ 2009-09-01 23:22 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-sql
OK, I'm waking up now. My locale is as Scott suspected, en-US.UTF-8,
and of
course my server too.
I guess I never really left "C" intellectually :) and we have a server that
thinks SQL-ASCII is cool and comparing lists of names and emails between
that server
and my local utf-8 one was rather perplexing.
I'm sure this a life-time's worth of discussion on the merits of
treating "."
as nothing when sorting....
Sorry for the noise.
Greg Stark wrote:
> On Tue, Sep 1, 2009 at 11:31 PM, Rob Sargent<robjsargent@gmail.com> wrote:
>
>> How many ways might one accidentally do that I wonder.
>>
>
> Well most operating system distributions ask you when you install them
> what region you're in and use a collation for that region.
>
> In 8.4 you can check what collation a database is set to use with \l
> in psql. In 8.3 the entire "cluster" has a single collation which you
> can see using "show lc_collate".
>
> You can see how your system's collations work by running sort:
>
> $ LC_ALL=c sort s
> a.ecke70@gmx.de
> a.fischedick@t-online.de
> adrianohazim@hotmail.com
> adx008@show.org.tw
> aecheniq@mac.com
> aelefant@unina.it
> aeo_tw@hotmail.com
> aflores3432@gmail.com
> afried@advancedneurosurgeons.com
> agave007@comcast.net
> agelsinger@amirsys.com
> agis1doc@yahoo.gr
>
> $ LC_ALL=en_US sort s
> adrianohazim@hotmail.com
> adx008@show.org.tw
> aecheniq@mac.com
> a.ecke70@gmx.de
> aelefant@unina.it
> aeo_tw@hotmail.com
> a.fischedick@t-online.de
> aflores3432@gmail.com
> afried@advancedneurosurgeons.com
> agave007@comcast.net
> agelsinger@amirsys.com
> agis1doc@yahoo.gr
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Odd sort behaviour
2009-09-01 21:01 Odd sort behaviour Rob Sargent <robjsargent@gmail.com>
2009-09-01 21:38 ` Re: Odd sort behaviour Scott Marlowe <scott.marlowe@gmail.com>
2009-09-01 22:31 ` Re: Odd sort behaviour Rob Sargent <robjsargent@gmail.com>
2009-09-01 23:04 ` Re: Odd sort behaviour Greg Stark <gsstark@mit.edu>
2009-09-01 23:22 ` Re: Odd sort behaviour Rob Sargent <robjsargent@gmail.com>
@ 2009-09-03 00:50 ` Lew <noone@lwsc.ehost-services.com>
0 siblings, 0 replies; 6+ messages in thread
From: Lew @ 2009-09-03 00:50 UTC (permalink / raw)
To: pgsql-sql
Rob Sargent wrote:
> I'm sure this a life-time's worth of discussion on the merits of
> treating "."
> as nothing when sorting....
Well, every sorted reference work in society at large seems to have a
different idea of how to sort - just compare the phone book to the dictionary.
That's the point of locales, to formalize such rules so that you can coerce
your system to follow one or another set as needed. That way you don't have
to agree or disagree with any rule, such as ignoring punctuation in the sort,
simply be aware of whether it applies to any given situation.
--
Lew
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2009-09-03 00:50 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-09-01 21:01 Odd sort behaviour Rob Sargent <robjsargent@gmail.com>
2009-09-01 21:38 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-09-01 22:31 ` Rob Sargent <robjsargent@gmail.com>
2009-09-01 23:04 ` Greg Stark <gsstark@mit.edu>
2009-09-01 23:22 ` Rob Sargent <robjsargent@gmail.com>
2009-09-03 00:50 ` Lew <noone@lwsc.ehost-services.com>
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