Forums PostgreSQL.fr

aldo31

Membre

[Order by] espaces et caractères spéciaux

Bonjour,

Je viens de déployer mon logiciel sur une distribution de Suse.
L'order by a un comportement étrange pour les espaces et les caractères spéciaux.

Normalement " non désigné" devrait apparaitre en premier grace à l'espace, mais sur cette version, il est positionné au niveau du "n".

la meme requete ne propose pas le meme tri sur Suse que sur Débian ou Fédora, Windows, ...
pourtant la locale de postgresql est la meme...

Comment ca se fait ?

gleu

Administrateur

Re : [Order by] espaces et caractères spéciaux

Ça dépend de la locale du serveur sur lequel est installé PostgreSQL. Et de la version de PostgreSQL aussi.

---

Guillaume.

gleu

Administrateur

Re : [Order by] espaces et caractères spéciaux

Au fait, quand vous dites "normalement", ce serait plutôt "avec un tri de type ASCII. Les français, les allemands et certainement d'autres ne trient pas forcément de cette façon là. D'où le fait que le tri dépend de la locale du serveur.

---

Guillaume.

aldo31

Membre

Re : [Order by] espaces et caractères spéciaux

Dans ce message :
"mauvais tri" signifie que l'espace n'est pas géré dans le tri
"bon tri" signifie que l'espace est géré en premier

sur la bd qui fait le mauvais tri, la locale est : "fr_FR.UT8-8" (distribitution suse et debian)

et sur celle qui a un comportement satisfaisant : "French_France.1252" (sur des distribution windows)
le tri fonctionne correctement sur des distributions debian et fédora, mais la je ne peux pas vérifier la locale qui est configurée dessus.

tu pense que le pb vient de la ?

gleu

Administrateur

Re : [Order by] espaces et caractères spéciaux

Oui. Un test simple pour te le montrer :

guillaume@laptop:~$ createdb b1 --locale fr_FR.UTF-8
guillaume@laptop:~$ createdb b2 --locale C --template template0
guillaume@laptop:~$ psql -l
                                 Liste des bases de données
    Nom    | Propriétaire | Encodage |     Tri     | Type caract. |     Droits d'accès      
-----------+--------------+----------+-------------+--------------+-------------------------
 b1        | guillaume    | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8  | 
 b2        | guillaume    | UTF8     | C           | C            | 
 benchs    | guillaume    | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8  | 
 benchs2   | benchs2      | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8  | 
 finality  | guillaume    | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8  | 
 jpa       | guillaume    | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8  | 
 postgres  | guillaume    | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8  | 
 template0 | guillaume    | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8  | =c/guillaume           +
           |              |          |             |              | guillaume=CTc/guillaume
 template1 | guillaume    | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8  | =c/guillaume           +
           |              |          |             |              | guillaume=CTc/guillaume
(9 lignes)

guillaume@laptop:~$ psql b1
psql (9.0rc1)
Saisissez « help » pour l'aide.

b1=# create table t1 (c1 text);
CREATE TABLE
b1=# insert into t1 values ('a'), ('b'), (' a'), (' b');
INSERT 0 4
b1=# select * from t1 order by c1;
 c1 
----
 a
  a
 b
  b
(4 lignes)

b1=# \q
guillaume@laptop:~$ psql b2
psql (9.0rc1)
Saisissez « help » pour l'aide.

b2=# create table t1 (c1 text);
CREATE TABLE
b2=# insert into t1 values ('a'), ('b'), (' a'), (' b');
INSERT 0 4
b2=# select * from t1 order by c1;
 c1 
----
  a
  b
 a
 b
(4 lignes)

b1 fait un tri en prenant en compte l'ordre contraint par la locale (donc en français, donc sans prendre en compte les espaces). b2 fait de même, mais avec une locale anglaise, donc un tri ASCII.

---

Guillaume.

Marc Cousin

Membre

Re : [Order by] espaces et caractères spéciaux

Puisque d'habitude c'est toi qui chipotes, gleu, cette fois-ci ce sera moi

C n'est pas la locale anglaise. Les locales anglaises sont les en_GB. Enfin si on veut encore chipoter, pas anglaises, mais grandes bretonnes.

C, c'est plutôt l'absence de locale. Donc messages dans la langue par défaut, tri ascii, etc?

---

Marc.

gleu

Administrateur

Re : [Order by] espaces et caractères spéciaux

Yep, t'as raison. Ça montre surtout que je n'ai pas voulu installer les locales anglaises sur mon poste

---

Guillaume.

Marc Cousin

Membre

Re : [Order by] espaces et caractères spéciaux

T'as raison. Sus à la perfide albion. On s'éloigne du sujet non ?

---

Marc.

aldo31

Membre

Re : [Order by] espaces et caractères spéciaux

C'est tres bizarre...

Chez toi :
createdb b1 --locale fr_FR.UTF-8
createdb b2 --locale C --template template0

le tri place l'espace en 1er dans la b2
et le tri ne gère pas l'espace dans la b1

sur mon poste de développement (sous vista), j'ai des tests, j'ai exactement le comportement inverse :

C:\Program Files\PostgreSQL\8.4\bin>psql -l -U postgres
                                               Liste des bases de donnÚes
            Nom             | PropriÚtaire | Encodage |        Tri         |Type caract.    |     Droits d'acc
----------------------------+-------------+----------+--------------------+--------------------+-----------------------
BD1               | postgres    | UTF8     | French_France.1252 | French_France.1252 |
BD2         | postgres    | UTF8     | C                  | C               |
.......

sur la BD1, le tri place l'élément : " [Non désigné]" en premier
et sur la BD2, le tri place cet élément au niveau du N

J'ai du mal à comprendre....

gleu

Administrateur

Re : [Order by] espaces et caractères spéciaux

Les définitions des locales dépendent du système d'exploitation. Donc, l'ordre ne sera pas forcément identique entre un Windows et un Linux (ou un Mac).

---

Guillaume.

aldo31

Membre

Re : [Order by] espaces et caractères spéciaux

alors quelle locale dois je mettre pour que ca fonctionne :
- sur Windows ?
- sur linux - Fédora ?
- sur linux - débian ?
- sur linux - Suse ?

Marc Cousin

Membre

Re : [Order by] espaces et caractères spéciaux

C'est ici que cela devient amusant (enfin, façon de parler).

PostgreSQL utilise les collations du système d'exploitation. Ce qui veut dire que votre ordre de tri ne dépend pas que de l'encodage choisi, mais aussi du système d'exploitation. Sous Linux, je pense que tous les ordres fr_FR auront l'espace comme dernier caractère.

Vous pouvez trouver des indices ici:
http://www.collation-charts.org/

En tout cas, il n'y a pas de solution unique. Et imaginer que le caractère espace arrivait avant tous les autres quelle que soit la collation était illusoire.

Vous feriez mieux de rechercher une méthode fiable permettant à l'enregistrement d'arriver toujours premier (on peut vous y aider, je pense, si vous nous expliquez ce que vous essayez exactement de résoudre avec vos blancs).

Dernière modification par Marc Cousin (09/09/2010 18:08:13)

---

Marc.

aldo31

Membre

Re : [Order by] espaces et caractères spéciaux

Bin en fait, je trie des éléments par ordre alphabétique, mais j'aimerai placer un élément : 'Non désigné' en premier. Donc jusqu'à présent je l'écrivais avec un '[' ou un ' ' devant pour que le order by le place en 1er.

Tu as une idée pour contourner ce pb ?

Marc Cousin

Membre

Re : [Order by] espaces et caractères spéciaux

Pas comme ça. À part avoir une colonne supplémentaire, qui indiquerait qu'un champ marqué de cette façon arrive avant les autres, auquel cas, il suffit de trier sur les deux colonnes à la fois (par exemple une colonne "champ_prioritaire" qui pourrait valoir true ou false).

Sinon, il y a toujours la méthode 'de bourrin' :

CREATE TABLE test (a varchar);
INSERT INTO test values ('a'),('b'),('c');
SELECT * from test order by (case when a='c' then '0' else a end);

base1=# SELECT * from test order by case when a='c' then NULL else a end NULLS FIRST;

a
---
c
a
b

OUAAAAIS, 'c' est avant 'a' 

C'est une façon pour forcer, en dur, un tri, pour des valeurs spéciales.

Dernière modification par Marc Cousin (09/09/2010 20:46:14)

---

Marc.

aldo31

Membre

Re : [Order by] espaces et caractères spéciaux

C'est vrai que c'est bourrin !!!
c'est bizarre qu'il n'y ait pas la possibilité de choisir un tri dans le postgresql.conf, ou au niveau des propriétés de la bd ou encore au niveau du order by...

il y a vraiment aucune autre solution ?

gleu

Administrateur

Re : [Order by] espaces et caractères spéciaux

Non. La possibilité d'indiquer une locale par colonne est prévue pour la 9.1, ie pour dans un an. Si le patch est bien écrit, accepté, commité, etc.

En attendant, votre seule solution est ce que propose Marc, à savoir : ORDER BY CASE WHEN col = ' non désigné' THEN NULL ELSE col END NULLS FIRST

---

Guillaume.

Marc Cousin

Membre

Re : [Order by] espaces et caractères spéciaux

vous pouvez définir votre opérateur de tri sur un type de données que vous allez définir (à partir du type text normal). Il vous faudra redéfinir toutes les règles de comparaison, etc… pour que ce type soit indexable avec votre nouvel ordre de tri. Tout cela de préférence en C pour que ça n'ait pas trop d'impact sur les performances.

De toutes façons, il n'y a aucune règle, nulle part, qui dit que le caractère espace devrait être avant les autres dans des ordres de collation. Il y en a même de nombreux qui disent qu'il doit tout simplement ne pas être pris en compte. À votre place, j'enlèverais ce blanc, qui est une bidouille, et je ferais le order by comme montré ci-dessus. Ce qui a comme intérêt de ne traiter la valeur «Non désigné» comme un cas particulier uniquement au moment de l'affichage dans votre liste.

---

Marc.

Marc Cousin

Membre

Re : [Order by] espaces et caractères spéciaux

gleu: même, la possibilité d'utiliser une locale par colonne en 9.1 ne garantira pas de trouver une locale compatible avec un ordre de collation arbitraire. Tous les tris fr_FR sous Linux considèrent que le caractère blanc est le dernier, il me semble (mais il faudrait regarder en détail dans le code de la glibc).

---

Marc.

aldo31

Membre

Re : [Order by] espaces et caractères spéciaux

est-ce que ceci fonctionnera ?
ORDER BY CASE WHEN col ilike ' %' or col ilike '[%' or col ilike '(%'
THEN NULL ELSE col END NULLS FIRST

gleu

Administrateur

Re : [Order by] espaces et caractères spéciaux

Oui, mais ça ne sera pas rapide.

---

Guillaume.