From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk> |
Cc: | Björn Lundin <b(dot)f(dot)lundin(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: unexpected (to me) sorting order |
Date: | 2015-04-09 12:23:08 |
Message-ID: | CAOR=d=2B6GCMVCAxnNJtFfPSDjsXO3vo8wNyz1ffzd=kJDq6Mw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Apr 8, 2015 at 3:33 AM, Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk> wrote:
>
>> From: Björn Lundin <b(dot)f(dot)lundin(at)gmail(dot)com>
>>To: pgsql-general(at)postgresql(dot)org
>>Sent: Wednesday, 8 April 2015, 10:09
>>Subject: [GENERAL] unexpected (to me) sorting order
>>
>>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;
>>
>>
>
>
>
> The collation of your "bnl" database is utf8, so the "." punctuation character is seen as a "variable element" and given a lower weighting in the sort to the rest of the characters. That's just how the collate algorithm works in UTF8.
utf8 is an encoding method, not a collation. The collation is en_US,
encoded in utf8. You can use C collation with utf8 encoding just fine.
So just replace UTF8 with en_US in your sentence and you've got it
right.
From | Date | Subject | |
---|---|---|---|
Next Message | Glyn Astill | 2015-04-09 12:43:56 | Re: unexpected (to me) sorting order |
Previous Message | Bill Moran | 2015-04-09 11:10:16 | Re: Regarding bytea column in Posgresql |