Compare software version in postgres

Question

Is there a way to compare the software version (e.g. XYZ> ABC) in postgres? I am looking for a function on / varchar strings or "version" types.

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

Thank you very much.

Answer 1

You can split the version into an array, and then do an 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; 

demonstration

Answer 2

Use the cheaper string_to_array() . There is 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; 

SQL Fiddle

Answer 3

Perhaps you can add the pl function, in my case I 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.