D
D
Denis Bondar2018-07-31 09:22:29
PostgreSQL
Denis Bondar, 2018-07-31 09:22:29

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;

The universal locale uni-u-kn-true is specified here because the locale is not known in advance. In this case, all three servers are assembled by the same installation script. Only the versions may differ, and possibly some dependencies that I don't know about yet.
2. We also tried to use solutions found on the Internet in the form of regular expressions in the ORDER operator and sentences like:
SELECT * ORDER BY (substring(house, '^[0-9]+'))::int, coalesce(substring(house, '[^0-9_].*$'),'');

But none of them gave the desired result.
In fact, only the collate option works correctly, but for some reason it only works on the developers' virtual machine under ubuntu. Here, in fact, I would like to find out how to make COLLATE work through ICU everywhere.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question