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 |
