Compare software version in postgres
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
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