Alphanumérique cas sensibles de tri dans postgres

Je suis nouveau sur postrges et du tri des colonnes de type varchar. envie de vous expliquer le problème avec l'exemple ci-dessous:

nom de la table: testsorting

   order       name
    1            b
    2            B
    3            a
    4            a1
    5            a11
    6            a2
    7            a20
    8            A
    9            a19

sensibles à la casse de tri (qui est par défaut dans postgres) donne:

select name from testsorting order by name;

    A
    B
    a
    a1
    a11
    a19
    a2
    a20
    b

cas sensibles de tri donne:

sélectionnez le nom de testsorting commande par UPPER(nom);

      A
      a
      a1
      a11
      a19
      a2
      a20
      B
      b

comment puis-je faire alphanumérique cas sensibles de tri dans postgres pour obtenir ci-dessous afin:

          a
          A
          a1
          a2
          a11
          a19
          a20
          b
          B

Je n'ai pas l'esprit de l'ordonnance de majuscules ou de minuscules, mais l'ordre doit être "aAbB" ou "AaBb" et ne doit pas être "ABab"

Veuillez indiquer si vous avez une solution à ce dans postgres.

OriginalL'auteur akhi | 2013-03-15

  1. 6

    Mon PostgreSQL sortes la façon dont vous le souhaitez. La façon PostgreSQL compare les chaînes de caractères est déterminé par les paramètres régionaux et de classement. Lorsque vous créez la base de données à l'aide de createdb il est -l option pour définir les paramètres régionaux. Vous pouvez aussi vérifier comment il est configuré dans votre environnement à l'aide de psql -l:

    [postgres@test]$ psql -l
List of databases
 Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
---------+----------+----------+------------+------------+-----------------------
 mn_test | postgres | UTF8     | pl_PL.UTF8 | pl_PL.UTF8 |

    Comme vous le voyez ma base de données utilise polonais classement.

    Si vous avez créé la base de données à l'aide d'autres de classement, alors vous pouvez utiliser d'autres classement des requêtes comme:

    SELECT * FROM sort_test ORDER BY name COLLATE "C";
SELECT * FROM sort_test ORDER BY name COLLATE "default";
SELECT * FROM sort_test ORDER BY name COLLATE "pl_PL";

    Vous pouvez liste des classements par:

    SELECT * FROM pg_collation;

    ÉDITÉ:

    Oh, j'en ai oublié que " a11 " doit être avant 'a2'.

    Je ne pense pas que la norme de classement peut résoudre le tri alphanumérique. Pour un tel tri, vous aurez à scinde une chaîne en pièces, tout comme dans la Clodoaldo Neto réponse. Une autre option est utile si vous avez fréquemment à l'ordre de cette façon, est de séparer le nom de domaine en deux colonnes. Vous pouvez créer de déclenchement lors de l'INSERTION et de mise à JOUR qui a divisé name en name_1 et name_2 et puis:

    SELECT name FROM sort_test ORDER BY name_1 COLLATE "en_EN", name_2;

    (J'ai changé de classement du polonais à l'anglais, vous devez utiliser votre propre classement pour trier les lettres aącć etc)

    Merci Michal. J'ai vérifié psql -l, mais pas en me montrant la configuration des paramètres régionaux. À l'aide de RASSEMBLER "pl_PL" SÉLECTIONNEZ travaillé et de trier la liste en cas de sensible toutefois le problème persiste avec la alphanumériques et "a2" est répertorié après "a11" et "a19". voulez-vous dire que l'utilisation d'un bon ASSEMBLER permettra de résoudre le tri alphanumérique?
    Voir mon édité répondre

    OriginalL'auteur Michał Niklas

  2. 5

    Si le nom est toujours dans le 1 alpha followed by n numerics format:

    select name
from testsorting
order by
    upper(left(name, 1)),
    (substring(name from 2) || '0')::integer
    J'obtiens l'ERREUR: invalid input syntaxe entier: "" avec ce. Ai-je besoin de vérifier les assembler?? Si je supprime ::integer à la fin, je suis de sortie ce qui n'est pas trié. a11, a19 avant de a2, ce qui est faux(A, A , a1,a11,a19,a2, a20, B, b). Le bon ordre de tri doit être Une, a , a1,a2,a11,a19,a20, B, b.
    Corrigés.
    Grâce Clodoaldo. J'ai eu cette erreur en raison de la valeur nulle, donc le besoin de la charge supplémentaire pour gérer la valeur null dans la démarche que vous avez suggéré. tous les commentaires?
    Une valeur null ne produit pas ce message d'erreur. Cochez sélectionner (substring(null à partir de 2) || '0')::entier. Quel est le message d'erreur exact?
    ah atlast trouvé ce que je voulais.

    OriginalL'auteur Clodoaldo Neto

  3. 0

    PostgreSQL utilise la bibliothèque C de paramètres régionaux installations de tri de chaînes. C bibliothèque est fournie par le système d'exploitation hôte. Sur Mac OS X ou un BSD-famille de système d'exploitation,l'UTF-8 définitions sont brisées, et par conséquent les résultats sont comme par classement "C".

    image ci-jointe pour le classement des résultats avec ubuntu 15.04 en OS hôte

    Vérifier les FAQ sur postgres wiki pour plus de détails : https://wiki.postgresql.org/wiki/FAQ

    OriginalL'auteur Manu Singhal

  4. 0

    Réponse fortement inspiré de cette une.

    En utilisant une fonction, il sera plus facile à garder propre si vous en avez besoin de plus de requêtes différentes.

    CREATE OR REPLACE FUNCTION alphanum(str anyelement)
   RETURNS anyelement AS $$
BEGIN
   RETURN (SUBSTRING(str, '^[^0-9]*'),
      COALESCE(SUBSTRING(str, '[0-9]+')::INT, -1) + 2000000);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

    Ensuite, vous pouvez utiliser de cette façon:

    SELECT name FROM testsorting ORDER BY alphanum(name);

    Test:

    WITH x(name) AS (VALUES ('b'), ('B'), ('a'), ('a1'),
   ('a11'), ('a2'), ('a20'), ('A'), ('a19'))
SELECT name, alphanum(name) FROM x ORDER BY alphanum(name);

 name |  alphanum   
------+-------------
 a    | (a,1999999)
 A    | (A,1999999)
 a1   | (a,2000001)
 a2   | (a,2000002)
 a11  | (a,2000011)
 a19  | (a,2000019)
 a20  | (a,2000020)
 b    | (b,1999999)
 B    | (B,1999999)

    OriginalL'auteur Le Droid

  5. -2

    Je suis d'accord avec Clodoaldo Neto de réponse, mais aussi n'oubliez pas d'ajouter l'index

    CREATE INDEX testsorting_name on testsorting(upper(left(name,1)), substring(name from 2)::integer)
    C'est l'indice, pas la commande par. Il ne sera pas trier votre liste, Clodoaldo Neto de réponse. Cela permettra de faire le tri efficace.
    Cet indice ne sera probablement pas être utilisés pour le tri. Seulement un index unique est utilisé pour le tri aussi loin que je peux voir ici avec 9.3. Je serais heureux de leur donner tort, mais...
    Toutes les index B-tree peut être utilisé pour un tri, indépendamment de si elle est unique: postgresql.org/docs/9.3/static/indexes-ordering.html. Dépend évidemment un index existant qui reflète la requête spécifique d'être faite.

    OriginalL'auteur Chris Aitchison