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}