Postgresql natural sort with numbers first

Question

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?

Answer 1

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.

Answer 2

Finally, I resolved the problem by creating additional property into Entity with @Formula:

@Formula(value = "name ~ '[^[:digit:]]'")
public String nonDigitName;

and now I have

Sort.by(Sort.Direction.ASC, "nonDigitName", "name")

I have one more challenge. I would like to move this property to another class MySortClass and make my base entity class extends MySortClass. But when I moved the property, I have

Unable to locate Attribute  with the the given name [nonDigitName] on this ManagedType [pl.ppl.szopl.app.baggagebelt.BaggageBeltEntity]

Is there any possibility to Sort by inherited fields?