Обсуждение: Odd sort behaviour
Since when does "." sort as "nothing at all"
This select
select distinct u.user_name from subscriber_user u, subscription s,
subscription_templatet 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)
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
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 >
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
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 > >
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 formalizesuch 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