比较Postgres中的软件版本
问题描述：
有没有办法在postgres中比较软件版本(例如X.Y.Z> A.B.C)?我正在搜索字符串/varchar或版本"类型的函数.
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.
我发现 http://pgxn.org/dist/semver/doc/semver.html ，但我正在寻找替代方案(部署起来不太容易.)
I found out that http://pgxn.org/dist/semver/doc/semver.html, but i'm looking for alternatives (not so easy to deploy..)
非常感谢.
答
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;