467,905 Members | 1,849 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,905 developers. It's quick & easy.

sorting and spaces in postgresql with en_US locale

We are having a weird problem that we ran into recently. If I use the
following statements to create a test table and then run the select statement
at the end, we get a very strange sort order. It appears that to do the
sorting, all the spaces are removed from the strings. It would appear that in
the example below 'ab e' should be before 'abd'.

create table testing_sort(col1 text);
insert into testing_sort values('a');
insert into testing_sort values('ab');
insert into testing_sort values('ab c');
insert into testing_sort values('abd');
insert into testing_sort values('ab e');

select * from testing_sort order by col1;
col1
------
a
ab
ab c
abd
ab e
(5 rows)

pg_controldata reports...
LC_COLLATE: en_US
LC_CTYPE: en_US

on another box, which has both LC_COLLAGE and LC_CTYPE set to C, the sorting
works as expected...

select * from testing_sort order by col1;
col1
------
a
ab
ab c
ab e
abd

Does anyone know if there is any other way to get the sorting to work as
expected short of doing an dumping, doing an initdb, and reloading? Or is
there some other setting that is causing the sort to do strange things.

The only work around we have found is to create a sort column and replace all
spaces with 0 and then sort on that column. Any other suggestions or
workarounds?

Issue was tested on both 7.4.1 and 7.3.4, both running on linux.

Thanks,

-Chris

--
Chris Kratz
Systems Analyst/Programmer
VistaShare LLC
www.vistashare.com

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #1
  • viewed: 3398
Share:
2 Replies
On Wed, 2004-07-21 at 20:58, Chris Kratz wrote:
We are having a weird problem that we ran into recently. If I use the
following statements to create a test table and then run the select statement
at the end, we get a very strange sort order. It appears that to do the
sorting, all the spaces are removed from the strings. It would appear that in
the example below 'ab e' should be before 'abd'. LC_COLLATE: en_US
LC_CTYPE: en_US


That is how the en_US (and en_GB) locales are defined.

Their sorting is dictionary style: spaces and capitalisation are
ignored. This is a glibc issue, not a PostgreSQL issue.

--
Oliver Elphick ol**@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"Greater love hath no man than this, that a man lay
down his life for his friends." John 15:13
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #2
Oliver Elphick wrote:
On Wed, 2004-07-21 at 20:58, Chris Kratz wrote:
We are having a weird problem that we ran into recently. If I use the
following statements to create a test table and then run the select
statement
at the end, we get a very strange sort order. It appears that to do the
sorting, all the spaces are removed from the strings. It would appear
that in the example below 'ab e' should be before 'abd'.

LC_COLLATE: en_US
LC_CTYPE: en_US


That is how the en_US (and en_GB) locales are defined.

Their sorting is dictionary style: spaces and capitalisation are
ignored. This is a glibc issue, not a PostgreSQL issue.


Is there any way to specify the collation to use for a sort, other than the
collation specified at initdb time?

For example, suppose I have a database that is running in the Foo locale,
and I have clients in the Bar locale. In the Foo locale 'Adam' sorts
before 'adam', wheras in the Bar locale 'adam' sorts before 'Adam'. Other
than doing the sort at the application level, is there any way to do this?

e.g. something like:
SELECT last_name, first_name
FROM people
ORDER BY last_name, first_name COLLATION = en_US;
Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Tim Edwards | last post: by
12 posts views Thread by Sander Smeenk | last post: by
2 posts views Thread by Daniel Gaudreault | last post: by
8 posts views Thread by Hitesh Bagadiya | last post: by
6 posts views Thread by Dennis Gearon | last post: by
6 posts views Thread by Jean-Guillaume LALANNE | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.