Sorting Varchar version in Postgres-CodePudding

I have table packages with column version(Varchar) and I cannot sort string literals just like that.

So is there any way I can get the latest package version?

Data is in this format :

id | version
128,3.0.5-1
128,3.0.6-1
128,3.0.7-1
128,3.0.8-1
128,3.0.9-1
128,3.0.13-2
128,3.0.4-1-1
128,3.0.10-1
128,3.0.11-2
128,3.0.11-1

I came to a solution something like:

SELECT  version
FROM packages
ORDER BY
    -- First, sort by the first number in the version
    SUBSTRING(version, '^[0-9] ') DESC,
    -- Then, sort by the second number in the version
    SUBSTRING(version, '[0-9] \\.[0-9] \\.([0-9] )-') DESC,
    -- Then, sort by the third number in the version
    CAST(SUBSTRING(version, '[0-9] \\.[0-9] \\.[0-9] -([0-9] )') AS INTEGER) DESC,
    -- Finally, sort by the fourth number in the version
    SUBSTRING(version, '[0-9] \\.[0-9] \\.[0-9] -[0-9] \\.([0-9] )') DESC
    -- Return only the first row

This returns :

3.0.5-1
3.0.6-1
3.0.7-1
3.0.8-1
3.0.9-1
3.0.13-2
3.0.4-1-1
3.0.10-1
3.0.11-2
3.0.11-1

It seems that this works fine until 3.0.10-1 so the problem is it follows x.x.x-x pattern but does not work when the x is two digits.

Is there any way I can achieve this within query itself? I would really appreciate if someone can give me hint or a solution.

CodePudding user response：

split_part() is better suited for this kind of splitting a string into elements.

You also need to convert each element to a number to get a correct sorting:

select *
from packages
order by split_part(version, '.', 1)::int,
         split_part(version, '.', 2)::int,
         string_to_array(split_part(version, '.', 3), '-')::int[]

The third part (e.g. 11-1') is converted to an integer array which can be sorted directly.

If you only had versions like 3.0.5 this could be sorted by converting the whole version number into an integer array. You can achieve this by replacing the minus sign with a dot:

select *
from packages
order by string_to_array(replace(version, '-', '.'), '.')::int[];

And to only get the latest package version:

select *
from packages
order by string_to_array(replace(version, '-', '.'), '.')::int[] desc
fetch first row only;

Demo: https://dbfiddle.uk/mBw2-OQC