[FIXED] Postgresql natural sort with numbers first

Issue

In Postgres I need to sort text with natural order, but with one exception - if the string has only number, it should be placed at top. So I need such order:

["98", "125", "134", "148", "265", "634", "1233", "5231",  "1m1ds", "1m2", "1m3", "1n3", "1w3r", "2m3", "2n3ds", "9t6","12gh", "13jy","25hg", "123y", "des2", "nme", "wer5"]

I tried with this:

CREATE COLLATION IF NOT EXISTS numeric (provider = icu, locale = 'en@colNumeric=yes');
ALTER TABLE "baggage_belts" ALTER COLUMN "name" type TEXT COLLATE numeric;

and it is ok, but numbers are mixed into numbers+text:

[1m1ds, 1m2, 1m3, 1n3, 1w3r, 2m3, 2n3ds, 9t6, 12gh, 13jy, 25hg, 98, 123y, 125, 134, 148, 265, 634, 1233, 5231, des2, nme, wer5]

Anyone has knowledge is it possible make it works with "empty" numbers first?

Solution

Then you should add a second ORDER BY expression that checks for non-digits:

ORDER BY name ~ '[^[:digit:]]', name

This relies on FALSE < TRUE, and you can support it with a two-column index defined like the ORDER BY clause.

Answered By - Laurenz Albe
Answer Checked By - Cary Denson (JavaFixing Admin)