pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (8+ messages)  latest in thread

Message-ID: <4C4416CB020000250003389C@gw.wicourts.gov>
Permalink:  ../4C4416CB020000250003389C@gw.wicourts.gov/
Also on:    postgresql.org/message-id/4C4416CB020000250003389C@gw.wicourts.gov

 · 

reply

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