Re: sort order - Mailing list pgsql-general

Hi,

context:

one postgres 12 on centos 7

one postgres 12 on windows 10

both on machines with french as default

the centos 7 lc_collate and lc_ctype: fr_FR.UTF_8

the w10 lc_collate and lc_ctype: French_France.1252

create table test (ble text, id serial primary key);

insert into test(ble) values(' ');

insert into test(ble) values('Marc');

insert into test(ble) values(' Marc');

insert into test(ble) values('marc');

insert into test(ble) values(' marc');

insert into test(ble) values('bobo');

insert into test(ble) values(' bobo'):

insert into test(ble) values('élise');

differences include a french é character, and some white spaces at the beginning.

then select * from test order by ble;

centos result:

  ble  | id
-------+----
       |  3
 bobo  |  2
  bobo |  1
 élise |  6
 marc  |  5
  marc |  4
 Marc  |  8
  Marc |  7
(8 lignes)

w10 result:

  ble  | id
-------+----
       |  3
  bobo |  1
  marc |  4
  Marc |  7
 élise |  8
 bobo  |  2
 marc  |  5
 Marc  |  6
(8 lignes)

so, obviously, both lc_collate knows about the é

but obviously, too, they do behave differently on the impact of the beginning white space.

I didn't see anything about this behaviour on the doc, unless the reference at the libc should be understood as please read and test libc doc on each platform.

So my first question is: why ?

My second question is: how to make the centos postgres behave like the w10 one ??

ie. knowing about french characters AND taking beginning white spaces into account ?

thanks,

regards,

 

Marc MILLAS

Senior Architect

+33607850334

www.mokadb.com