Буквенно-цифровой регистр в чувствительной сортировке в postgres

Я новичок в postrges и хочу сортировать столбцы типа varchar. хочу объяснить проблему с помощью приведенного ниже примера:

имя таблицы: testsorting

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

сортировка по регистру (по умолчанию в postgres) дает:

select name from testsorting order by name;

    A
    B
    a
    a1
    a11
    a19
    a2
    a20
    b

чувствительная к регистру сортировка дает:

выберите имя из порядка тестирования по UPPER (имя);

как я могу сделать буквенно-цифровой регистр в чувствительной сортировке в postgres, чтобы получить ниже порядка:

Я не буду учитывать порядок для капитальных или небольших букв, но порядок должен быть "aAbB" или "AaBb" и не должен быть "ABab"

Пожалуйста, предложите, если у вас есть решение для этого в postgres.

Ответ 1

Мой PostgreSQL сортируется так, как вы хотите. То, как PostgreSQL сравнивает строки, определяется локалью и сопоставлением. Когда вы создаете базу данных с помощью createdb, существует опция -l для установки языка. Также вы можете проверить, как он настроен в вашей среде, используя psql -l:

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

Как вы видите, моя база данных использует польскую сортировку.

Если вы создали базу данных, используя другую сортировку, вы можете использовать другую сортировку в запросе, как:

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

Вы можете просмотреть доступные сортировки:

SELECT * FROM pg_collation;

Редакция:

О, я пропустил, что "a11" должен быть до "a2".

Я не думаю, что стандартная сортировка может решить буквенно-цифровую сортировку. Для такой сортировки вам придется разделить строку на части, как в ответе Clodoaldo Neto. Другой вариант, который полезен, если вам часто приходится заказывать этот путь, заключается в том, чтобы разделить поле имени на два столбца. Вы можете создать триггер на INSERT и UPDATE, которые разделяют name на name_1 и name_2, а затем:

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

(Я изменил сопоставление с польского языка на английский, вы должны использовать свой родной набор для сортировки букв, например aącć и т.д.)

Ответ 2

Если имя всегда находится в формате 1 alpha followed by n numerics, тогда:

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

Ответ 3

PostgreSQL использует локальные возможности библиотеки C для сортировки строк. Библиотека C предоставляется операционной системой хоста. В Mac OS X или операционной системе семейства BSD определения локали UTF-8 разбиты, и, следовательно, результаты соответствуют настройке "C".

изображение, прикрепленное для результатов сопоставления с ubuntu 15.04 в качестве хост-системы

Проверьте часто задаваемые вопросы по wiki для postgres для более подробной информации: https://wiki.postgresql.org/wiki/FAQ

Ответ 4

Ответ сильно вдохновлен этим.
Используя функцию, будет легче очистить ее, если вам это нужно по разным запросам.

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;

Тогда вы можете использовать его следующим образом:

SELECT name FROM testsorting ORDER BY alphanum(name);

Тест:

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)

Ответ 5

Я согласен с ответом Clodoaldo Neto, но также не забудьте добавить индекс

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