pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWeird sorting order
8+ messages / 4 participants
[nested] [flat]
* Weird sorting order
@ 2010-07-16 12:14 Robert Voinea <robert.voinea@topex.ro>
0 siblings, 1 reply; 8+ messages in thread
From: Robert Voinea @ 2010-07-16 12:14 UTC (permalink / raw)
To: pgsql-admin
Hi...
I have the following table:
CREATE TABLE test
(
id SERIAL PRIMARY KEY,
val VARCHAR(32) NOT NULL
);
INSERT INTO test VALUES
(DEFAULT, '##34''),
(DEFAULT, '##32'),
(DEFAULT, '##31'),
(DEFAULT, '2ff'),
(DEFAULT, '##26'),
(DEFAULT, '2##33'),
(DEFAULT, '2##25'),
(DEFAULT, '2##24'),
(DEFAULT, '2##23'),
(DEFAULT, '211'),
(DEFAULT, '210'),
(DEFAULT, '203'),
(DEFAULT, '202'),
(DEFAULT, '201'),
(DEFAULT, '200');
Why is it that when running the query:
SELECT * FROM test ORDER BY val;
I get the following result?
id | val
----+-------
1 | 200
2 | 201
3 | 202
4 | 203
5 | 210
6 | 211
7 | 2##23
8 | 2##24
9 | 2##25
10 | 2##33
11 | ##26
12 | 2ff
13 | ##31
14 | ##32
15 | ##34
(15 rows)
Shouldn't value '2ff' be placed right after '211' but before '2##23'?
Tested on PostgreSQL 8.2, 8.4, Linux SuSE & Kubuntu.
Thank you!...
--
Robert Voinea <robert (dot) voinea (at) topex (dot) ro>
Software Developer
Phone: +40 21 408 38 00 / ext. 343
Fax: +40 21 408 38 08
Local time: GMT+2
http://www.topex.ro
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Weird sorting order
@ 2010-07-16 15:34 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Robert Voinea <robert.voinea@topex.ro>
0 siblings, 1 reply; 8+ messages in thread
From: Kevin Grittner @ 2010-07-16 15:34 UTC (permalink / raw)
To: pgsql-admin; Robert Voinea <robert.voinea@topex.ro>
Robert Voinea <robert.voinea@topex.ro> wrote:
> Why is it that when running the query:
>
> SELECT * FROM test ORDER BY val;
>
> I get the following result?
> id | val
> ----+-------
> 1 | 200
> 2 | 201
> 3 | 202
> 4 | 203
> 5 | 210
> 6 | 211
> 7 | 2##23
> 8 | 2##24
> 9 | 2##25
> 10 | 2##33
> 11 | ##26
> 12 | 2ff
> 13 | ##31
> 14 | ##32
> 15 | ##34
> (15 rows)
>
> Shouldn't value '2ff' be placed right after '211' but before
> '2##23'?
That depends on your collation configuration. What do you get
from?:
show lc_collate;
In many collations, special characters such as '#' are ignored.
-Kevin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Weird sorting order
@ 2010-07-16 15:34 Bill MacArthur <webmaster@dhs-club.com>
0 siblings, 0 replies; 8+ messages in thread
From: Bill MacArthur @ 2010-07-16 15:34 UTC (permalink / raw)
To: pgsql-admin
It's ignoring the hash marks. It's like they are invisible characters.
Tested on 8.1.16
-------- Original Message --------
Subject: [ADMIN] Weird sorting order
Date: Fri, 16 Jul 2010 15:14:09 +0300
From: Robert Voinea <robert.voinea@topex.ro>
Organization: Topex
To: pgsql-admin@postgresql.org
Hi...
I have the following table:
CREATE TABLE test
(
id SERIAL PRIMARY KEY,
val VARCHAR(32) NOT NULL
);
INSERT INTO test VALUES
(DEFAULT, '##34''),
(DEFAULT, '##32'),
(DEFAULT, '##31'),
(DEFAULT, '2ff'),
(DEFAULT, '##26'),
(DEFAULT, '2##33'),
(DEFAULT, '2##25'),
(DEFAULT, '2##24'),
(DEFAULT, '2##23'),
(DEFAULT, '211'),
(DEFAULT, '210'),
(DEFAULT, '203'),
(DEFAULT, '202'),
(DEFAULT, '201'),
(DEFAULT, '200');
Why is it that when running the query:
SELECT * FROM test ORDER BY val;
I get the following result?
id | val
----+-------
1 | 200
2 | 201
3 | 202
4 | 203
5 | 210
6 | 211
7 | 2##23
8 | 2##24
9 | 2##25
10 | 2##33
11 | ##26
12 | 2ff
13 | ##31
14 | ##32
15 | ##34
(15 rows)
Shouldn't value '2ff' be placed right after '211' but before '2##23'?
Tested on PostgreSQL 8.2, 8.4, Linux SuSE & Kubuntu.
Thank you!...
--
Robert Voinea <robert (dot) voinea (at) topex (dot) ro>
Software Developer
Phone: +40 21 408 38 00 / ext. 343
Fax: +40 21 408 38 08
Local time: GMT+2
http://www.topex.ro
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Attachments:
SpamAssassinReport.txt (text/plain, 974B) download
Spam detection software, running on the system "mail.dhs-club.com", has
identified this incoming email as possible spam. The original message
has been attached to this so you can view it (if it isn't spam) or label
similar future email. If you have any questions, see
postmaster@dhs-club.com for details.
Content preview: Hi... I have the following table: CREATE TABLE test ( id SERIAL
PRIMARY KEY, val VARCHAR(32) NOT NULL ); [...]
Content analysis details: (2.0 points, 5.0 required)
pts rule name description
---- ---------------------- --------------------------------------------------
0.6 RCVD_IN_SORBS_WEB RBL: SORBS: sender is a abuseable web server
[89.38.174.194 listed in dnsbl.sorbs.net]
0.0 BAYES_50 BODY: Bayesian spam probability is 40 to 60%
[score: 0.5015]
1.4 AWL AWL: From: address is in the auto white-list
* Re: Weird sorting order
@ 2010-07-19 05:23 Robert Voinea <robert.voinea@topex.ro>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 8+ messages in thread
From: Robert Voinea @ 2010-07-19 05:23 UTC (permalink / raw)
To: pgsql-admin; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
On Friday 16 July 2010 18:34:06 Kevin Grittner wrote:
> Robert Voinea <robert.voinea@topex.ro> wrote:
> > Why is it that when running the query:
> >
> > SELECT * FROM test ORDER BY val;
> >
> > I get the following result?
> >
> > id | val
> >
> > ----+-------
> >
> > 1 | 200
> > 2 | 201
> > 3 | 202
> > 4 | 203
> > 5 | 210
> > 6 | 211
> > 7 | 2##23
> > 8 | 2##24
> > 9 | 2##25
> >
> > 10 | 2##33
> > 11 | ##26
> > 12 | 2ff
> > 13 | ##31
> > 14 | ##32
> > 15 | ##34
> >
> > (15 rows)
> >
> > Shouldn't value '2ff' be placed right after '211' but before
> > '2##23'?
>
> That depends on your collation configuration. What do you get
> from?:
>
> show lc_collate;
>
> In many collations, special characters such as '#' are ignored.
=> show lc_collate;
lc_collate
-------------
en_US.UTF-8
--
Robert Voinea <robert (dot) voinea (at) topex (dot) ro>
Software Developer
Phone: +40 21 408 38 00 / ext. 343
Fax: +40 21 408 38 08
Local time: GMT+2
http://www.topex.ro
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Weird sorting order
@ 2010-07-19 14:11 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Robert Voinea <robert.voinea@topex.ro>
0 siblings, 1 reply; 8+ messages in thread
From: Kevin Grittner @ 2010-07-19 14:11 UTC (permalink / raw)
To: pgsql-admin; Robert Voinea <robert.voinea@topex.ro>
Robert Voinea <robert.voinea@topex.ro> wrote:
> => show lc_collate;
> lc_collate
> -------------
> en_US.UTF-8
I'm afraid the order you're seeing is what you're supposed to get
for that collation sequence. In that collation, special characters
(including spaces) are only used as tie-breakers for values which
are tied when the special characters are ignored. There may be a
few consequences of that which you haven't yet found. As one
example:
test=# show lc_collate;
lc_collate
-------------
en_US.UTF-8
(1 row)
test=# create table t1 (c1 text);
CREATE TABLE
test=# insert into t1 values ('one'),(' one'),('one
'),('##one'),('one##');
INSERT 0 5
test=# select '"' || c1 || '"' from t1 order by c1;
?column?
----------
"one"
" one"
"##one"
"one "
"one##"
(5 rows)
test=# select '"' || c1 || '"' from t1 order by c1 desc;
?column?
----------
"one##"
"one "
"##one"
" one"
"one"
(5 rows)
For that reason, we have (so far) used the C locale, which provides
the binary sort you probably expected, and we use special columns,
maintained by triggers, to control selection and sequencing as
needed -- for example we have a "searchName" column in any table
where we have name columns, which is forced into a canonical format.
FWIW, our algorithm for generating a canonical name also excludes
spaces and the '#' character, although it still has significant
differences from the en_US.UTF-8 collation.
-Kevin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Weird sorting order
@ 2010-07-19 14:28 Burgholzer, Robert (DEQ) <Robert.Burgholzer@deq.virginia.gov>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 8+ messages in thread
From: Burgholzer, Robert (DEQ) @ 2010-07-19 14:28 UTC (permalink / raw)
To: pgsql-admin
This is really interesting. Is there a way to set the locale on the fly
for a sort? Having looked at the docs, it appears as if the initDB step
is the only opportunity to do so.
Regards,
r.b.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Weird sorting order
@ 2010-07-19 16:15 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Burgholzer, Robert (DEQ) <Robert.Burgholzer@deq.virginia.gov>
0 siblings, 1 reply; 8+ messages in thread
From: Kevin Grittner @ 2010-07-19 16:15 UTC (permalink / raw)
To: Robert (DEQ) Burgholzer <Robert.Burgholzer@deq.virginia.gov>; pgsql-admin
Burgholzer, Robert (DEQ)" <Robert.Burgholzer@deq.virginia.gov>
wrote:
> This is really interesting. Is there a way to set the locale on
> the fly for a sort? Having looked at the docs, it appears as if
> the initDB step is the only opportunity to do so.
Starting with 8.4 you can specify it at the database level:
http://www.postgresql.org/docs/8.4/interactive/sql-createdatabase.html
Peter Eisentraut is working on a way to set the collation for an
individual column or (I think) for a sort; but that won't be in 9.0.
Hopefully in 9.1....
http://archives.postgresql.org/message-id/1279045531.32647.14.camel@vanquo.pezone.net
-Kevin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Weird sorting order
@ 2010-07-21 05:56 Robert Voinea <robert.voinea@topex.ro>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 8+ messages in thread
From: Robert Voinea @ 2010-07-21 05:56 UTC (permalink / raw)
To: pgsql-admin; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Robert (DEQ) Burgholzer <Robert.Burgholzer@deq.virginia.gov>
On Monday 19 July 2010 19:15:32 Kevin Grittner wrote:
> Burgholzer, Robert (DEQ)" <Robert.Burgholzer@deq.virginia.gov>
>
> wrote:
> > This is really interesting. Is there a way to set the locale on
> > the fly for a sort? Having looked at the docs, it appears as if
> > the initDB step is the only opportunity to do so.
>
> Starting with 8.4 you can specify it at the database level:
>
> http://www.postgresql.org/docs/8.4/interactive/sql-createdatabase.html
>
> Peter Eisentraut is working on a way to set the collation for an
> individual column or (I think) for a sort; but that won't be in 9.0.
> Hopefully in 9.1....
>
> http://archives.postgresql.org/message-id/1279045531.32647.14.camel@vanquo.
> pezone.net
>
> -Kevin
Thank you for clarifying this.
--
Robert Voinea <robert (dot) voinea (at) topex (dot) ro>
Software Developer
Phone: +40 21 408 38 00 / ext. 343
Fax: +40 21 408 38 08
Local time: GMT+2
http://www.topex.ro
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2010-07-21 05:56 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-07-16 12:14 Weird sorting order Robert Voinea <robert.voinea@topex.ro>
2010-07-16 15:34 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-19 05:23 ` Robert Voinea <robert.voinea@topex.ro>
2010-07-19 14:11 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-19 14:28 ` Burgholzer, Robert (DEQ) <Robert.Burgholzer@deq.virginia.gov>
2010-07-19 16:15 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-07-21 05:56 ` Robert Voinea <robert.voinea@topex.ro>
2010-07-16 15:34 Re: Weird sorting order Bill MacArthur <webmaster@dhs-club.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