Compare software versions 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..)

Answer 1

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