Problem ordering accented characters in PostgreSQL 8.4

Question

I create my database using fr_CA.UTF-8 like this:

createdb --encoding=utf-8 --locale=fr_CA.UTF-8 --template=template0 someapp_development

Then I load sample data, which contains accented characters, including "Étude de Me André Caron" and "Zellers inc.". In French, É should sort / collate before Z, and thus I expect the following statement:

SELECT fullname FROM addressees ORDER BY LOWER(fullname)

to return É before Z, but sadly, this isn't the case:

# select fullname from addressees where party_id in (36, 618, 1264, 2481, 4473) order by lower(fullname);
                 fullname                  
-------------------------------------------
 VRV Express inc. [4473]
 Vêtements S.P. Apparels inc. (Les) [2481]
 Zellers inc. (Galeries Orford) [1264]
 Étude de Me André Caron [618]
 Étude de Me Richard Drapeau [36]

Notice additionally that VRV was before Vêtements. I must have misunderstood something somewhere. Some more information:

$ psql someapp_development
psql (8.4.4, server 8.4.3)

$ psql --list
                                          List of databases
           Name            |   Owner   | Encoding |  Collation  |    Ctype    |   Access privileges   
---------------------------+-----------+----------+-------------+-------------+-----------------------
 meetphil_development      | francois  | UTF8     | fr_CA.UTF-8 | fr_CA.UTF-8 |

I have read stackoverflow.com/questions/1659158/…. Is it basically the same problem? — François Beausoleil
– François Beausoleil, Commented Jun 28, 2011 at 18:56
Yeah, but you might want to try it with Postgres 9.0 or 9.1 beta on a test server, because a lot of work has gone into collation since 8.4. In fact, I'm 99% this'll work on 9.1, since they added a whole page of docs on this very topic in that version. — Denis de Bernardy
– Denis de Bernardy, Commented Jun 28, 2011 at 19:21
Postgres uses the system locale support. You most probably could find out if it's a bug in the system by creating a few files with names similar to those entries in "fullname" and list the containing directory. — Milen A. Radev
– Milen A. Radev, Commented Jun 28, 2011 at 20:38

Milen A. Radev · Accepted Answer · 2011-06-28 21:01:32Z

2

My conclusion after some tests (Postgres 8.4.7 on Scientific Linux 6.0) is that's most probably a bug in the system fr_CA locale:

-bash-4.1$ psql
psql (8.4.7)
Saisissez « help » pour l'aide.

postgres=# show lc_collate ;
 lc_collate
------------
 fr_CA.utf8
(1 ligne)

postgres=# create table addressees (party_id serial primary key, fullname text);
NOTICE:  CREATE TABLE créera des séquences implicites « addressees_party_id_seq » pour la colonne serial « addressees.party_id »
NOTICE:  CREATE TABLE / PRIMARY KEY créera un index implicite « addressees_pkey » pour la table « addressees »
CREATE TABLE
postgres=# insert into addressees (fullname) values ('VRV Express inc. [4473]'),('Vêtements S.P. Apparels inc. (Les) [2481]'),('Zellers inc. (Galeries Orford) [1264]'), ('Étude de Me André Caron [618]'),('Étude de Me Richard Drapeau [36]');
INSERT 0 5
postgres=# select * from addressees order by lower(fullname);
 party_id |                 fullname
----------+-------------------------------------------
        4 | Étude de Me André Caron [618]
        5 | Étude de Me Richard Drapeau [36]
        2 | Vêtements S.P. Apparels inc. (Les) [2481]
        1 | VRV Express inc. [4473]
        3 | Zellers inc. (Galeries Orford) [1264]
(5 lignes)

postgres=#

answered Jun 28, 2011 at 21:01

Milen A. Radev

63.2k22 gold badges111 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

François Beausoleil Over a year ago

I'm on Mac OS X. I'll run some tests on Linux to see if it fixes the issue.

François Beausoleil Over a year ago

I booted an EC2 instance of PostgreSQL 8.4 on Ubuntu 11.04 Natty and things were as I expected, even though I created the database using en_US.UTF-8 collation. I reran my tests using the fr_CA.UTF-8 locale and things were still fine. Thanks for the pointer!

Collectives™ on Stack Overflow

Problem ordering accented characters in PostgreSQL 8.4

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related