Forums PostgreSQL.fr

comicway

Membre

Tris naturel des string

Bonjour à tous,

Voilà je cherche si il y a une fonction dans le order by pour effectuer un tris naturel sur des string,
par ex j'ai ceci

a
b
A
d
B
1
10
2
3
11

Je voudrai avoir un tris comme ceci:
a
A
b
B
d
1
2
3
10
11

ou eventuellement les chiffres avant les lettres.

Sous mysql c'est l'operateur +0 mais sous postgresql j'ai pas trouvé

Merci d'avance
Christophe

gleu

Administrateur

Re : Tris naturel des string

L'ordre du tri dépend du paramètre lc_collate. Celui-ci est configuré lors de la création d'une base de données en 8.4 et lors du initdb avant cette version. Cependant, je ne crois pas qu'il existe un ordre de tri pour les chaînes de caractères qui trie les nombres comme vous le voulez.

---

Guillaume.

FNo

Membre

Re : Tris naturel des string

Bonjour,
et quelque chose comme

  SELECT mon_champ FROM ma_table ORDER BY UPPER(mon_champ);

La fonction upper à l'air d'aider au tri en plaçant les champs à null, puis les nombres et enfin les lettres.

Dernière modification par FNo (14/12/2009 17:07:06)

gleu

Administrateur

Re : Tris naturel des string

Ça ne fonctionnera pas sur les lettres accentuées. Je ne suis pas sûr que ça fonctionne non plus pour les nombres.

---

Guillaume.

FNo

Membre

Re : Tris naturel des string

Je n'ai pas essayé les lettres accentuées, mais pour le reste cela fonctionne plutôt bien, y compris les décimaux. Cela dépend peut être de la configuration, aussi.

comicway

Membre

Re : Tris naturel des string

hello,

Je viens de trouver un natural sort basé sur des fonctions
http://drupalcode.org/viewvc/drupal/con … iew=markup
je viens d'essayer et c'est assez rapide...

gleu

Administrateur

Re : Tris naturel des string

Du traitement de chaîne en PL/pgsql ? c'est voué à être super lent.

---

Guillaume.

comicway

Membre

Re : Tris naturel des string

Sur quel volumétrie ca peut devenir lent? est ce que tu as des articles ou je peux me documenter?
J'ai testé hier sur 50000 entrées.

Est ce que la solution ne serait pas de créer 1 table annexe et lorsque j'insere les données dans ma table j'ajoute dans la table annexe avec le traitement sur le string (suppression des blancs, uppercase, transformation des nombres,...)
Et je fait une recherche sur la table annexe et une jointure sur la table avec toutes les données

gleu

Administrateur

Re : Tris naturel des string

Ça dépend surtout du nombre de caractères à trier, plutôt que du nombre de lignes. Avoir une table annexe ralentira les opérations de modification sur la table principale. Donc tout dépend si vous avez beaucoup de modifications sur celle-ci.

---

Guillaume.

comicway

Membre

Re : Tris naturel des string

En fait, je suis occupé à réaliser un systeme d'indexation de fichiers, j'ai donc une table directory avec les noms des repertoires et une table files avec le nom des fichiers.
Il y a peu de modification sur les tables directory, files; soit c'est des ajouts soit des suppressions, mais tout le reste se fait sur les tables liées (commentaires, données exif, droits d'acces,...)
quel serait la meilleur méthode pour avoir un temps de reponse acceptable et un tris possible comme cité au premier post?
Est ce que si je fait:

directory (id, name ,...)
directory_name_sorting (id, name)

file (id, filename, ...)
file_filename_sorting (id, filename)

Lorsque j'ajoute un record dans file, j'ajoute un record dans file_filename_sorting avec un formatage (suppression des blancs, uppercase, transformation des nombres,...)

Et j'ai plus qu'a faire un select * from file LEFT JOIN file_filename_sorting fls ON  fls.id = file.id ORDER BY fls.filename

Est ce que ça serait une solution envisageable et pas trop gourmande?

Merci pour votre aide
Christophe

Martin Flahault

Membre

Re : Tris naturel des string

Bonjour,

je suis en train de tester postgres et je n'arrive absolument pas à recréer un comportement facile à obtenir sur MySQL ou Primebase.

J'ai une table "bidule" avec une colonne "libelle" de type char(40) dans une base "salutlesmickeys".
J'aimerai que les ORDER BY sur cette colonne soient insensibles à la casse et aux accents. Or, pour l'instant, ce n'est pas le cas. Mon serveur est en version 8.4.2. Toute l'install est en UTF8 :

salutlesmickeys=# \encoding
UTF8

salutlesmickeys=# SHOW LC_COLLATE;
lc_collate 
-------------
fr_FR.UTF-8
(1 row)

salutlesmickeys=# SHOW LC_CTYPE;
lc_ctype   
-------------
fr_FR.UTF-8
(1 row)

J'aimerai un système qui me permette de typer la colonne pour que je n'ai pas à modifier toutes mes requêtes existantes.
En MySQL, ça marche tout seul... Sur Primebase, qui est le SGBD que j'utilise pour l'instant, j'avais créé une "COLLATING SEQUENCE" qui définissait l'ordre de tri des caractères, puis des domaines de données char() utilisant cet ordre de tri prédéfini :
CREATE DOMAIN petiteString CHAR[10],  ORDER AS COLLATING SEQUENCE maSequenceDeTri;

Ce système permet de n'utiliser que des ORDER BY simples dans les requêtes.

Je n'ai pas trouvé de réponse (ou je ne les ai pas comprises) dans les échanges précédents sur le sujet.

Merci pour votre aide,

Martin

gleu

Administrateur

Re : Tris naturel des string

J'aimerai un système qui me permette de typer la colonne pour que je n'ai pas à modifier toutes mes requêtes existantes.

Ça n'existe pas sous PostgreSQL.

Avant la 8.4, l'ordre de tri était défini au niveau de l'instance. Donc chaque base avait le même ordre de tri.

En 8.4, vous pouvez définir l'ordre du tri sur la base. Donc toutes les colonnes des tables de cette base auront ce même type de tri.

Ce qui me donne:

guillaume@laptop:~$ createdb b3
guillaume@laptop:~$ psql b3
psql (8.4.2)
Saisissez « help » pour l'aide.

b3=# show lc_collate;
 lc_collate  
-------------
 fr_FR.UTF-8
(1 ligne)

b3=# create table t1 (contenu text);
CREATE TABLE
b3=# insert into t1 values ('b'), ('a'), ('c'), ('à'), ('E'), ('A'), ('e');
INSERT 0 7
b3=# select * from t1;
 contenu 
---------
 b
 a
 c
 à
 E
 A
 e
(7 lignes)

b3=# select * from t1 order by contenu;
 contenu 
---------
 a
 A
 à
 b
 c
 e
 E
(7 lignes)

Donc, j'ai bien un tri ne prenant pas en compte accents et majuscules.

---

Guillaume.

gleu

Administrateur

Re : Tris naturel des string

Et pour vous donner un exemple avec un autre ordre de tri:

guillaume@laptop:~$ createdb b4  --locale C --template template0
guillaume@laptop:~$ psql b4
psql (8.4.2)
Saisissez « help » pour l'aide.

b4=# show lc_collate;
 lc_collate 
------------
 C
(1 ligne)

b4=# create table t1 (contenu text);
CREATE TABLE
b4=# insert into t1 values ('b'), ('a'), ('c'), ('à'), ('E'), ('A'), ('e');
INSERT 0 7
b4=# select * from t1;
 contenu 
---------
 b
 a
 c
 à
 E
 A
 e
(7 lignes)

b4=# select * from t1 order by contenu;
 contenu 
---------
 A
 E
 a
 b
 c
 e
 à
(7 lignes)

---

Guillaume.

Marc Cousin

Membre

Re : Tris naturel des string

Bonjour.
Pour commencer, merci d'éviter d'utiliser une discussion déjà en cours pour poser une question, la prochaine fois créez en un neuf.

Si vous voulez un type de chaîne insensible à la casse, ce que vous recherchez s'appelle citext (case insensitive text) http://docs.postgresql.fr/8.4/citext.html
Par contre, je ne connais pas de façon pour trier en ignorant les accents.

---

Marc.

Martin Flahault

Membre

Re : Tris naturel des string

@gleu

Je viens de refaire ton exemple sur ma machine. C'est bien ce que je craignais, ça ne fonctionne pas sur mon installation. Pour info, voici ce que j'obtiens :

Martin:bin martin$ ./createdb b3
Martin:bin martin$ ./psql b3
psql (8.4.1)
Type "help" for help.

b3=# show lc_collate;
lc_collate 
-------------
fr_FR.UTF-8
(1 row)

b3=# create table t1 (contenu text);
CREATE TABLE
b3=# insert into t1 values ('b'), ('a'), ('c'), ('à'), ('E'), ('A'), ('e');
INSERT 0 7
b3=# select * from t1;
contenu
---------
b
a
c
à
E
A
e
(7 rows)

b3=# select * from t1 order by contenu;
contenu
---------
A
E
a
b
c
e
à
(7 rows)

Certes, mon install est une 8.4.1 et pas une 8.4.2 mais je ne crois pas que cela fasse vraiment de différence. En revanche, mon install est faite sur un Mac OS 10.6.2 et quand je passe le "SHOW LC_COLLATE" j'obtiens "1 row" et toi "1 ligne". Y'a t'il une différence de localisation entre nos 2 installations qui aurait un rapport avec mon problème ?

Merci d'avance,

Martin

gleu

Administrateur

Re : Tris naturel des string

Il semble que les locales UTF-8 ne sont pas correctes sous les BSD. Or Mac est un BSD. Donc soit vous choisissez une autre locale (lors de l'initdb), soit vous changez de système d'exploitation.

---

Guillaume.

Martin Flahault

Membre

Re : Tris naturel des string

J'ai déjà un parc installé d'une centaine de serveur fonctionnant sur un autre SGBD avec des bases en UTF8. Le changement d'encodage ou de plateforme est donc délicat.

Où peut-on poster pour faire part de ce genre de problème ?
Y'a t'il un correctif possible à attendre ?

Si rien, j'irai voir vers d'autres SGBD mais Postgres à l'air vraiment bien sur pleins de points.

gleu

Administrateur

Re : Tris naturel des string

Où peut-on poster pour faire part de ce genre de problème ?

Si c'est un problème Mac OS X comme je le pense, je suppose qu'il faut voir avec Apple.

Si vous croyez à un problème de PostgreSQL, vous pouvez toujours demander sur la liste pgsql-general mais la réponse que je vous ai donné vient de là. Si vous voulez les détails, c'est sur http://archives.postgresql.org/message- … ersoft.com . À noter que la dernière réponse vient d'un codeur PostgreSQL expérimenté.

Y'a t'il un correctif possible à attendre ?

Comme à mon avis le problème ne vient pas de PostgreSQL, il ne faut pas s'attendre à un correctif sur PostgreSQL même.

Et comme je ne crois pas à un correctif d'Apple...

Si rien, j'irai voir vers d'autres SGBD mais Postgres à l'air vraiment bien sur pleins de points.

Ce serait bien dommage.

---

Guillaume.