Необходимо реализовать натуральную сортировку строкового поля в PostgreSQL. Поле содержит номера домов, в которых могут встречаться буквы и следующие за буквами цифры. Например: 10, 2, 1, 35-а, 12, 12а, 14к1, 14к2, М-12, М-13а и т.д.
Необходимо отсортировать результаты выборки по этому полю таким образом, чтобы номера домов находились в натуральном порядке. Конкретная локаль заранее неизвестна и определяется во время установки разрабатываемого приложения для конкретного клиента.
Были проделаны следующие попытки:
1. Пытались использовать свой коллате через провайдер
ICU. На машине разработчиков под vagrant (ubuntu 16) всё работает без проблем, однако на стейджинге сортировка работает как обычная сортировка, а не как натуральная (там Debian 9). Попытались ради эксперимента выполнить тоже самое на другом сервере с Debian 8 - там коллате вообще не создался по причине отсутствия провайдера icu. Создавали коллате и проверяли его работу следующим образом (консоль psql):
CREATE COLLATION numeric (provider = icu, locale = 'uni-u-kn-true');
SELECT '2' < '10' COLLATE numeric;
Здесь указана универсальная локаль
uni-u-kn-true потому как локаль заранее неизвестна. При этом все три сервера собраны одним и тем же установочным скриптом. Могут отличаться только версии и, возможно, какие-то зависимости, о которых я пока не знаю.
2. Также пытались использовать найденные на просторах интернет решения в виде регулярных выражений в операторе ORDER и предложений вида:
SELECT * ORDER BY (substring(house, '^[0-9]+'))::int, coalesce(substring(house, '[^0-9_].*$'),'');
Но ни одно из них не дало нужного результата.
Фактически верно работает только вариант с collate, но почему-то он работает только на виртуалке разработчиков под убунтой. Вот, собственно, хотелось бы выяснить, каким образом заставить работать COLLATE через ICU везде.
Средний
