Re: Collate order on Mac OS X, text with diacritics in UTF-8

Here is an exemple :

postgres=# create database newbase;
CREATE DATABASE
postgres=# \c newbase;
psql (8.4.2)
You are now connected to database "newbase".
newbase=# create table t1 (contenu text);
CREATE TABLE
newbase=# insert into t1 values ('a'), ('e'), ('à'), ('é'), ('A'), ('E');
INSERT 0 6

newbase=# select * from t1 order by contenu;
contenu
---------
A
E
a
e
à
é
(6 rows)

newbase=# select * from t1 order by upper(contenu);
contenu
---------
a
A
e
E
à
é
(6 rows)

Here is the encoding informations :

newbase=# \encoding
UTF8
newbase=# show lc_collate;
lc_collate
------------
fr_FR
(1 row)

newbase=# show lc_ctype;
lc_ctype
----------
fr_FR
(1 row)

As with others DBMS (MySQL for example), diacritics should be ignored when determining the sort order. Here is the expected output:
a
à
A
e
é
E

It seems there is a problem with the collating order on BSD systems with diacritics using UTF8.
If you put this text :
a
A
à
é
e
E

in a UTF8 text file and use the "sort" command on it, you will have the same wrong output as with PostgreSQL :
A
E
a
e
à
é

Hope this will help,

Martin