Answer the question
In order to leave comments, you need to log in
How to implement PostgreSQL natural sort?
It is necessary to implement natural sorting of a string field in PostgreSQL. The field contains house numbers, which can contain letters and numbers following the letters. For example: 10, 2, 1, 35-a, 12, 12a, 14k1, 14k2, M-12, M-13a, etc.
It is necessary to sort the results of the sample by this field in such a way that the house numbers are in natural order. The specific locale is not known in advance and is determined during the installation of the application being developed for a specific client.
The following attempts have been made:
1. Tried to use my collate through the ICU provider. On the vagrant development machine (ubuntu 16) everything works without problems, but on staging, sorting works like normal sorting, and not like natural sorting (Debian 9 there). For the sake of experiment, we tried to do the same on another server with Debian 8 - there the collate was not created at all due to the absence of the icu provider. We created a collate and checked its work as follows (psql console):
CREATE COLLATION numeric (provider = icu, locale = 'uni-u-kn-true');
SELECT '2' < '10' COLLATE numeric;
SELECT * ORDER BY (substring(house, '^[0-9]+'))::int, coalesce(substring(house, '[^0-9_].*$'),'');
Answer the question
In order to leave comments, you need to log in
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question