Эквивалент Oracle nls_sort в postgres

Question

Работаем над миграцией БД с Oracle на Postgres. Я пытаюсь добиться такого поведения сортировки в своем приложении, где буквенно-цифровая метка, начинающаяся с цифр, должна стоять после букв (как описано в примере ниже).

Я попробовал французскую сортировку в Postgres, но не смог добиться того же результата, который получил в Oracle, используя NLS_SORT "french".

Отправляя запросы Oracle, мне нужен эквивалент в Postgres, который можно установить в БД один раз, чтобы приложение впоследствии использовало его.

Оракул:

ALTER SESSION SET NLS_SORT='FRENCH';
ALTER SESSION SET NLS_COMP=LINGUISTIC;
select LABEL from CARBON order by LABEL ;

 LABEL
    APPLE
    BALL
    102C
    108C
    108D

Postgres:

Я попытался создать сопоставление с именем french из pg_collation вход fr-FR-x-icu и измените существующий столбец таблицы с помощью графического интерфейса pgAdmin следующим образом:

label character varying(255) COLLATE public.french NOT NULL

Не повезло, я получаю тот же результат.

select LABEL from CARBON order by LABEL ;

LABEL
102C
108C
108D
APPLE
BALL

Я пробовал следующий запрос также в Postgres, он дает тот же результат, что и выше. Я что-то упустил в Postgres?

select LABEL from CARBON order by LABEL collate "fr_FR"

Answer 1

Вы можете легко решить эту проблему, создав настраиваемую сортировку ICU (доступную в PostgreSQL v10), которая сортирует цифры больше, чем латинские символы:

CREATE COLLATION weird (
   LOCALE = 'fr-u-kr-latn-digit',
   PROVIDER = 'icu'
);

ALTER TABLE carbon ALTER label TYPE text COLLATE weird;

SELECT * FROM carbon ORDER BY label COLLATE weird;

 label 
-------
 APPLE
 BALL
 102C
 108C
 108D
(5 rows)

Это предполагает недавнюю библиотеку ICU, старые версии имеют несколько другой синтаксис.

В более старых версиях библиотеки ICU вам, возможно, придется использовать

CREATE COLLATION digitslast (
   PROVIDER = 'icu',
   LOCALE = 'fr@colReorder=latn-digit'
);