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

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 2

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.

Answer 3

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 4

You can split the version to array and then do array comparison.

select regexp_split_to_array(v1, '\.')::int[] v1, 
       regexp_split_to_array(v2, '\.')::int[] v2,
       regexp_split_to_array(v1, '\.')::int[] > regexp_split_to_array(v2, '\.')::int[] cmp
from versions;

demo

Answer 5

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;

SQL Fiddle.