postgresql - Clasificación alfanumérica in-sensitiva en postgres

postgresql-9.1

Question

Soy nuevo en postrges y quiero ordenar columnas de tipo varchar. quiero explicar el problema con el siguiente ejemplo:

nombre de la tabla: testorting

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

La clasificación sensible a mayúsculas y minúsculas (que es predeterminada en postgres) proporciona:

select name from testsorting order by name;

    A
    B
    a
    a1
    a11
    a19
    a2
    a20
    b

El caso de clasificación in-sensible da:

seleccione el nombre del orden de prueba de prueba por SUPERIOR (nombre);

      A
      a
      a1
      a11
      a19
      a2
      a20
      B
      b

¿Cómo puedo hacer que la clasificación alfanumérica entre mayúsculas y minúsculas en postgres para obtener orden inferior :

          a
          A
          a1
          a2
          a11
          a19
          a20
          b
          B

No me importa el orden de mayúsculas o minúsculas, pero el orden debe ser "aAbB" o "AaBb" y no debe ser "ABab"

Por favor sugiera si tiene alguna solución a esto en postgres.

Answer 1

Estoy de acuerdo con la respuesta de Clodoaldo Neto, pero también no olvide agregar el índice

CREATE INDEX testsorting_name on testsorting(upper(left(name,1)), substring(name from 2)::integer)

Answer 2

Mi PostgreSQL ordena la forma que quieras. La forma en que PostgreSQL compara cadenas se determina por la configuración regional y la intercalación. Cuando creas una base de datos con createdb existe la opción -l para establecer la configuración regional. También puede verificar cómo está configurado en su entorno usando 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 |

Como ves mi base de datos utiliza la colación polaca.

Si creó la base de datos utilizando otra intercalación, puede utilizar otra intercalación en la consulta, como:

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";

Puede enumerar las colaciones disponibles por:

SELECT * FROM pg_collation;

EDITADO:

Oh, me perdí que ''a11'' debe estar antes de ''a2''.

No creo que la clasificación estándar pueda resolver la ordenación alfanumérica. Para tal clasificación, tendrá que dividir la cadena en partes como en la respuesta de Clodoaldo Neto. Otra opción que es útil si con frecuencia tiene que ordenar de esta manera es separar el campo de nombre en dos columnas. Puede crear un activador en INSERTAR y ACTUALIZAR ese name división en name_1 y name_2 y luego:

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

(Cambié la intercalación de polaco a inglés, debería usar su intercalación nativa para ordenar letras como aącć, etc.)

Answer 3

PostgreSQL usa las facilidades de configuración regional de la biblioteca C para ordenar cadenas. La biblioteca C es proporcionada por el sistema operativo del host. En Mac OS X o en un sistema operativo de la familia BSD, las definiciones de configuración regional UTF-8 están rotas y, por lo tanto, los resultados son según la clasificación "C".

imagen adjunta para resultados de intercalación con ubuntu 15.04 como sistema operativo host

Consulte las Preguntas frecuentes en la wiki de postgres para obtener más detalles: https://wiki.postgresql.org/wiki/FAQ

Answer 4

Respuesta fuertemente inspirada de esta .
Al usar una función, será más fácil mantenerla limpia si la necesita en diferentes consultas.

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;

Entonces podrías usarlo de esta manera:

SELECT name FROM testsorting ORDER BY alphanum(name);

Prueba:

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)

Answer 5

Si el nombre está siempre en el 1 alpha followed by n numerics formato 1 alpha followed by n numerics , entonces:

select name
from testsorting
order by
    upper(left(name, 1)),
    (substring(name from 2) || ''0'')::integer