Question 113916 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql natural sort with numbers first

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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?


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search