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
