Forums PostgreSQL.fr

herve.lefebvre

Membre

COLLATE insatisfaisant

Bonjour,

Je n'arrive pas à faire un simple ORDER BY :-D

Je suis en fr_FR.utf8 :

herve=# select datname,datcollate,datctype from pg_database;
      datname      | datcollate  |  datctype   
-------------------+-------------+-------------
 template1         | fr_FR.UTF-8 | fr_FR.UTF-8
 template0         | fr_FR.UTF-8 | fr_FR.UTF-8
 postgres          | fr_FR.UTF-8 | fr_FR.UTF-8
 herve             | fr_FR.UTF-8 | fr_FR.UTF-8
 MyApp             | fr_FR.UTF-8 | fr_FR.UTF-8
 myapp_development | fr_FR.UTF-8 | fr_FR.UTF-8
 myapp_test        | fr_FR.UTF-8 | fr_FR.UTF-8
(7 lignes)

Mon problème, c'est que lors des tris, les espaces ou apostrophes en milieux de chaînes sont ignorés. Dans l'exemple suivant, je pense que théoriquement "aba ax" devrait être AVANT "abaa" par exemple.

herve=# select '*'||lib||'*' from t1 order by lib;
  ?column?   
-------------
 *   *
 *aba *
 *abaa*
 *aba ax*
 *abab*
 *eminem*
 *eminence*
 *éminence*
 *eminences*
 *éminences*
 *laamour*
 *lamour*
 *l amour*
 *l'amour*
 *lzamour*
(15 lignes)

En utilisant un collate POSIX, les espaces sont bien pris en compte, mais du coup les caractères accentués sont classés en fin d'alphabet :

herve=# select '*'||lib||'*' from t1 order by lib collate "POSIX";
  ?column?   
-------------
 *   *
 *aba *
 *aba ax*
 *abaa*
 *abab*
 *eminem*
 *eminence*
 *eminences*
 *l amour*
 *l'amour*
 *laamour*
 *lamour*
 *lzamour*
 *éminence*
 *éminences*
(15 lignes)

Quelqu'un aurait une idée pour que les espaces / apostrophes en milieu de chaîne soient pris en compte, et les caractères accentués gérés correctement ?

(comportements observés en 9.3 et 9.5, Linux).

Dernière modification par herve.lefebvre (12/01/2018 09:33:06)

herve.lefebvre

Membre

Re : COLLATE insatisfaisant

J'ai trouvé un début de réponse ici : https://www.postgresql.org/message-id/t … lcom-lxa05

Je vais creuser.

Si quelqu'un a déjà été confronté au problème je suis toujours preneur.

herve.lefebvre

Membre

Re : COLLATE insatisfaisant

Donc c'est bien lié au fichier /usr/share/i18n/locales/iso14651_t1_common de la distribution Linux. Je mets ici un hack un peu dégueulasse mais qui pourra toujours être utile à ceux qui rencontrent le même problème.
Afin que les espaces et apostrophes en milieu de chaînes soient correctement triés :

1)    Éditer le fichier /usr/share/i18n/locales/iso14651_t1_common

Remplacer la ligne :

<U0020> IGNORE ;IGNORE ;IGNORE;<U0020> # 32 <SP>

Par :

<U0020> <BAS>;<MIN>;IGNORE;<U0020> # 32 <SP>

Et la ligne :

<U0027> > IGNORE ;IGNORE ;IGNORE;<U0027> # 61 '

Par :

<U0027> <BAS>;<MIN>;IGNORE;<U0027> # 61 '

2)    Éditer le fichier /usr/share/i18n/locales/iso14651_t1_common et ajouter un espace après un commentaire. C’est juste pour modifier le hash du fichier.
3)    Régénérer les locales avec la commande « locale-gen » :

herve@herve-VirtualBox:/var/lib/locales/supported.d > sudo locale-gen
Generating locales...
  en_AG.UTF-8... up-to-date
  en_AU.UTF-8... up-to-date
  en_BW.UTF-8... up-to-date
  en_CA.UTF-8... up-to-date
  en_DK.UTF-8... up-to-date
  en_GB.UTF-8... up-to-date
  en_HK.UTF-8... up-to-date
  en_IE.UTF-8... up-to-date
  en_IN.UTF-8... up-to-date
  en_NG.UTF-8... up-to-date
  en_NZ.UTF-8... up-to-date
  en_PH.UTF-8... up-to-date
  en_SG.UTF-8... up-to-date
  en_US.UTF-8... up-to-date
  en_ZA.UTF-8... up-to-date
  en_ZM.UTF-8... up-to-date
  en_ZW.UTF-8... up-to-date
  fr_BE.UTF-8... done
  fr_CA.UTF-8... done
  fr_CH.UTF-8... done
  fr_FR.ISO-8859-1... done
  fr_FR.UTF-8... done
  fr_LU.UTF-8... done
Generation complete.

4)    Redémarrer le serveur pgsql :

herve@herve-VirtualBox:/var/lib/locales/supported.d > sudo service postgresql restart
[sudo] password for herve: 
 * Restarting PostgreSQL 9.3 database server                                                                                                                                             [ OK ] 
 * Restarting PostgreSQL 9.5 database server                                                                                                                                             [ OK ]

Maintenant le tri s'effectue comme attendu :

herve=# select '*'||lib||'*' from t1 order by lib collate "fr_FR";
  ?column?   
-------------
*   *
*aba *
*aba ax*
*abaa*
*abab*
*eminem*
*eminence*
*éminence*
*eminences*
*éminences*
*l amour*
*l'amour*
*laamour*
*lamour*
*lzamour*
(15 lignes)

Dernière modification par herve.lefebvre (12/01/2018 11:24:58)

dverite

Membre

Re : COLLATE insatisfaisant

Si vous faites ça il faut aussi réindexer tous les index qui utilisent cette collation dans toutes les bases de cette machine. Car ils sont tous invalides après ça.

Personnellement je suggérerais d'étudier une variante où on crée une nouvelle collation dans Linux sans toucher à l'existant. Suivi d'un CREATE COLLATION dans PostgreSQL.

---

@DanielVerite
http://blog-postgresql.verite.pro/

herve.lefebvre

Membre

Re : COLLATE insatisfaisant

A vrai dire je m'inquiète moins pour les index qui sont faciles à recréer que pour les effets de bords sur le serveur ( tris dans les script shell etc.) donc je crois qu'effectivement la création d'un fr_PG va s'imposer.

Quoiqu'il en soit, avoir des espaces et des apostrophes ignorés dans les tris alphabétiques, cela me paraît relever du bug dans la distro Linux.

gleu

Administrateur

Re : COLLATE insatisfaisant

Je suis d'accord avec Daniel, mieux vaut créer une nouvelle collation que modifier une existante. Surtout qu'à la prochaine mise à jour des collations, la modification sera écrasée.

---

Guillaume.