unexpected (to me) sorting order

From: Björn Lundin <b(dot)f(dot)lundin(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: unexpected (to me) sorting order
Date: 2015-04-08 09:09:42
Message-ID: CA++12HttJ+eGC1bYGeBgxeNZSsq959B5J0ZzgvW0hdT0P7=V3g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi!
below are some commands to
replicate a strange sorting order.

I do not see why id:s 3-6 are in the middle of the result set.
What am I missing?

begin;

create table T_SORT (
ID bigint default 1 not null , -- Primary Key
NAME varchar(100) default ' ' not null
);
alter table T_SORT add constraint T_SORTP1 primary key (
ID
);

insert into T_SORT values ( 1,'FINISH_110_150_1');
insert into T_SORT values ( 2,'FINISH_110_200_1');
insert into T_SORT values ( 3,'FINISH_1.10_20.0_3');
insert into T_SORT values ( 4,'FINISH_1.10_20.0_4');
insert into T_SORT values ( 5,'FINISH_1.10_30.0_3');
insert into T_SORT values ( 6,'FINISH_1.10_30.0_4');
insert into T_SORT values ( 7,'FINISH_120_150_1');
insert into T_SORT values ( 8,'FINISH_120_200_1');

select * from T_SORT order by NAME ;

rollback;
id | name
----+--------------------
1 | FINISH_110_150_1
2 | FINISH_110_200_1
3 | FINISH_1.10_20.0_3
4 | FINISH_1.10_20.0_4
5 | FINISH_1.10_30.0_3
6 | FINISH_1.10_30.0_4
7 | FINISH_120_150_1
8 | FINISH_120_200_1
(8 rows)

why is FINISH_1.10_20.0_3 between
FINISH_110_200_1 and
FINISH_120_150_1
?

That is why is '.' between 1 and 2 as in 110/120 ?

pg_admin III reports the database is created like
CREATE DATABASE bnl
WITH OWNER = bnl
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
CONNECTION LIMIT = -1;

bnl=> select version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 9.3.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.6.3
20120306 (Red Hat 4.6.3-2), 64-bit
(1 row)

psql says
psql (9.3.5, server 9.3.3)
It is an Amazon RDS-service

client machine

bnl(at)prod:~$ uname -a
Linux prod 3.2.0-4-amd64 #1 SMP Debian 3.2.63-2+deb7u1 x86_64 GNU/Linux

bnl(at)prod:~$ locale
LANG=en_US.UTF-8
LANGUAGE=
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=

--
/Björn

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Glyn Astill 2015-04-08 09:33:00 Re: unexpected (to me) sorting order
Previous Message Ramesh T 2015-04-08 07:56:57 Re: Stalled post to pgsql-general