Encodage de base de données Postgresql

Je suis tombé sur un problème d'encodage de ma base de données OSM dernièrement.  Mon besoin était pourtant très simple: je voulais imprimer sur ma carte les noms de rue en MAJUSCULES.  Je pensais donc utiliser la fonction upper() de postgresql via un mapfile, mais étrangement les caractères accentués ne suivaient pas:

osm= select upper('échap');
upper
-------
éCHAP
(1 row)

L'encodage da la base de données était bien UTF-8 puisque tous les caractères accentués apparaissaient convenablement.  Par contre le "Ctype" était à la valeur C. Dans la documentation de Postgresql on y mentionne que:

initdb initializes the database cluster's default locale and character set encoding. The character set encoding, collation order (LC_COLLATE) and character set classes (LC_CTYPE, e.g. upper, lower, digit) can be set separately for a database when it is created. initdb determines those settings for the template1 database, which will serve as the default for all other databases.

postges=l
Name    | Owner    | Encoding  | Collate |    Ctype
osm     | osm      | UTF8      | C       |    C

On a pas vraiment l'habitude de se soucier de cette valeur, mais elle affecte le fonctionnement de certaines fonctions qui traitent les caractères.  Pour contourner le problème, il y a deux options:

  1. Créer une nouvelle COLLATION dans Postgresql;
  2. Recréer le cluster de base de données;

Option 1

Une solution temporaire de contournement serait de créer une nouvelle COLLATION dans la base de données.  Avant on doit s'assurer d'avoir la Collation sur le serveur:

sudo locale-gen fr_CA.UTF-8 en_CA.UTF-8

Ensuite, on peux ajouter une nouvelle Collation correspondant dans la Base de données:

osm= CREATE COLLATION fr_ca_c (LOCALE='fr_CA.utf8');
osm= select upper('échap' COLLATE "fr_ca_c");;
upper
-------
ÉCHAP
(1 row)

Option 2

Cette option plus drastique sera plus robuste et efficiente à long terme.  Elle consiste à recréer le cluster de base de données pour supporter le bon encodage et la bonne Collation(Ctype). En principe, on doit être en mesure de créer la base de données avec les bons réglages de la façon suivante:

sudo su postgres -c'createdb -E UTF8 --lc-ctype en_CA.UTF-8 -T template0 template_postgis'
sudo su postgres -c'createlang -d template_postgis plpgsql;'
sudo su postgres -c'psql -U postgres -d template_postgis -c"CREATE EXTENSION postgis;"'
# # sudo su postgres -c'psql -U postgres -d template_postgis -c"select postgis_lib_version();"'
sudo su postgres -c'psql -U postgres -d template_postgis -c "GRANT ALL ON geometry_columns TO PUBLIC;"'
sudo su postgres -c'psql -U postgres -d template_postgis -c "GRANT ALL ON spatial_ref_sys TO PUBLIC;"'
sudo su postgres -c'psql -U postgres -d template_postgis -c "GRANT ALL ON geography_columns TO PUBLIC;"'
Par la suite, on doit refaire une nouvelle base de données avec ce template et le problème sera réglé: La création d'une nouvelle base de données avec le template UTF-8:
 sudo su postgres
 createdb -E utf8 -T template_postgis osm
 psql -d osm
 postgres= CREATE USER osm WITH PASSWORD 'osm';
 postgres= GRANT ALL PRIVILEGES ON DATABASE osm to osm;
 postgres=\l
 ----------------------------------
 Name | Owner | Encoding | Collate | Ctype
 osm | osm | UTF8 | C | en_CA.UTF-8
 postgres=\q
Il est possible d'avoir des problèmes de création de cette bd J'ai eu des problèmes de création d'une base de données template Postgresql supportant les caractères accentués et/ou l'encodage UTF-8.
sudo su postgres -c'createdb -E UTF8 --lc-ctype en_CA.UTF-8 -T template0 template_postgis'
createdb: database creation failed: ERROR:  invalid locale name en_CA.UTF-8

Comme mentionné plus haut, Postgresql va créer un moteur cluster (service, fichiers config, fichiers de données, logfile, etc) de base de données et ce moteur utilise les configurations d'encodages au moment de l'installation.  Si par mégarde, l'encodage UTF-8 n'était pas disponible, il faut détruire le cluster Postgresql avec pg_dropcluster, mettre à jour les variables locales d'encodage, et en créer un nouveau cluster avec pg_createcluster:

NOTE: Attention vous allez perdre toutes les BD de votre serveur!
sudo /etc/init.d/postgres stop
sudo su postgres
export LANGUAGE=en_CA.UTF-8
export LANG=en_CA.UTF-8
export LC_ALL=en_CA.UTF-8
locale-gen en_CA.UTF-8
pg_dropcluster --stop 9.1 main
pg_createcluster 9.1 main
exit
sudo /etc/init.d/postgres start
Juste pour être certain que tout est ok avec l'encodage de votre user :
export LANGUAGE=en_CA.UTF-8
export LANG=en_CA.UTF-8
export LC_ALL=en_CA.UTF-8
sudo localedef -i en_CA -f UTF-8 en_CA
sudo locale-gen en_CA.UTF-8
sudo update-locale
sudo sudo dpkg-reconfigure locales