From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: pgsql-admin@postgresql.org
To: Robert Voinea <robert.voinea@topex.ro>
Subject: Re: Weird sorting order
Date: Mon, 19 Jul 2010 09:11:39 -0500
Message-ID: <4C4416CB020000250003389C@gw.wicourts.gov> (raw)
In-Reply-To: <201007190823.14092.robert.voinea@topex.ro>
References: <201007161514.09960.robert.voinea@topex.ro>
<4C40359E0200002500033711@gw.wicourts.gov>
<201007190823.14092.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, robert.voinea@topex.ro
Subject: Re: Weird sorting order
In-Reply-To: <4C4416CB020000250003389C@gw.wicourts.gov>
References: <201007161514.09960.robert.voinea@topex.ro> <4C40359E0200002500033711@gw.wicourts.gov> <201007190823.14092.robert.voinea@topex.ro> <4C4416CB020000250003389C@gw.wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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