Compare software version in postgres

Question

Is there a way to compare software version (e.g. X.Y.Z > A.B.C) in postgres ? I'm searching for a function on string/varchar or a "version" type.

I found out that http://pgxn.org/dist/semver/doc/semver.html, but i'm looking for alternatives (not so easy to deploy..)

Thanks a lot.

Answer 1

Use the cheaper string_to_array(). There is no need for expensive regular expressions here:

SELECT string_to_array(v1, '.')::int[] AS v1
     , string_to_array(v2, '.')::int[] AS v2
     ,(string_to_array(v1, '.')::int[] > string_to_array(v2, '.')::int[]) AS cmp
FROM   versions;

db<>fiddle here
_{Old sqlfiddle}

Answer 2

As already suggested an easy way is to work with a numeric format of the version. The variable 'server_version_num' contains a numeric format of the version.

Eg.

• version 9.5.2 => 90502
• version 9.6.0 => 90600

• version 10.5 => 100500

  select current_setting('server_version_num')

return a number that can be easily compared with another version number.

Answer 3

An alternative approach is to use

SHOW server_version_num;

This returns a version number that's simpler to compare. e.g. 90610 for 9.6.10.

Update

Clarifying this answer based on the comments below. The version number produced here is machine readable. It is designed to be unique and avoid clashes.

e.g. Postgres version 12.1 will produce 120001 while 12.10 will produce 120010, etc

https://database.guide/how-to-check-your-postgresql-version/

Answer 4

Maybe you can add a pl function, in my case I have used python and distutils.version:

CREATE FUNCTION _is_major (a text, b text)
  RETURNS boolean
AS $$
    from distutils.version import LooseVersion 
    return LooseVersion(a) > LooseVersion(b)
$$ LANGUAGE PLPYTHONU;

You need the postgresql-plpython package.

Answer 5

Going a bit further Erwin's answer, we can create a function to compare a software version with a requirement (like in ruby). I've wrote a function that does just that, to use it:

SELECT semver_match('4.2.0', '>= 4.0'); -- TRUE

Here's the code and tests:

CREATE OR REPLACE FUNCTION semver_match(version text, req text) RETURNS boolean
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT
    AS $$ 
    SELECT CASE
    WHEN req LIKE '~>%' THEN
        string_to_array(version, '.')::int[] >= string_to_array(substring(req from 4), '.')::int[]
        AND
        string_to_array(version, '.')::int[] <
        -- increment last item by one. (X.Y.Z => X.Y.(Z+1))
        array_append(
            (string_to_array(substring(req from 4), '.')::int[])[1:(array_length(string_to_array(req, '.'), 1) - 1)], -- X.Y
            (string_to_array(substring(req from 4), '.')::int[])[array_length(string_to_array(req, '.'), 1)] + 1 -- Z + 1
        )
    WHEN req LIKE '>%' THEN string_to_array(version, '.')::int[] > string_to_array(substring(req from 3), '.')::int[]
    WHEN req LIKE '<%' THEN string_to_array(version, '.')::int[] < string_to_array(substring(req from 3), '.')::int[]
    WHEN req LIKE '>=%' THEN string_to_array(version, '.')::int[] >= string_to_array(substring(req from 4), '.')::int[]
    WHEN req LIKE '<=%' THEN string_to_array(version, '.')::int[] <= string_to_array(substring(req from 4), '.')::int[]
    WHEN req LIKE '=%' THEN 
        (string_to_array(version, '.')::int[])[1:array_length(string_to_array(substring(req from 3), '.'), 1)] = 
        string_to_array(substring(req from 3), '.')::int[]
    ELSE NULL
    END $$;

-- tests.
SELECT 
    ver,
    req,
    CASE WHEN semver_match(ver, req) = expected
    THEN '?' ELSE '?' END AS test_passed
FROM (VALUES
    ('2.3.1', '> 2.3', TRUE),
    ('2.3.1', '< 2.3.2', TRUE),
    ('2.3.1', '~> 2.3.2', FALSE),
    ('2.4.3', '~> 2.3.2', FALSE),
    ('2.3.2', '~> 2.3.2', TRUE),
    ('2.3.2', '= 2.3.2', TRUE),
    ('2.3.2', '= 2.3', TRUE),
    ('2.3.2', '= 2.4', FALSE)
) AS _ (ver, req, expected)

See my blogpost on that topic for more implementation details.