Compare software versions in Postgres

Use string_to_array(). No need for expensive regular expressions.

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;

fiddle
_{Old sqlfiddle}

Assuming all parts of the version number are valid integer numbers, of course.

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 written 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 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[] > 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[])[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',   '>= 2.3',   TRUE),
    ('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.

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.

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/

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.