PostgreSQL中Varchar类型版本号排序及获取最新版本问题

问题背景 #

packages表的version字段为Varchar类型，无法直接按字符串排序得到正确的版本顺序。现有版本号包含多位数数字（如3.0.10-1）及字符串后缀（如3.0.5-1-test-dev），原查询因固定位置截取字符串导致排序失效，且无法处理带字符串后缀的版本。

解决方案 #

方案1：无需扩展，纯SQL处理 #

利用PostgreSQL的数组排序特性，提取版本号中的数字部分形成整数数组，同时处理后缀规则（稳定版无后缀优先于带后缀的预发布版）：

SELECT version
FROM packages
ORDER BY
    -- 提取所有数字部分转为整数数组，按数组降序排序
    string_to_array(regexp_replace(version, '[^\d]+', ',', 'g'), ',')::int[] DESC,
    -- 稳定版（无字母后缀）排前，预发布版（带字母后缀）排后
    CASE WHEN version ~ '-[a-zA-Z]' THEN 1 ELSE 0 END ASC,
    -- 后缀部分按字典序降序（可根据需求调整为ASC）
    regexp_replace(version, '^[\d.-]+', '', 'g') DESC
LIMIT 1;

说明：

• regexp_replace(version, '[^\d]+', ',', 'g') 将所有非数字字符替换为逗号，得到数字序列字符串（如3.0.10-1-test-dev变为3,0,10,1）
• 转为整数数组后，PostgreSQL会按数组元素依次比较，解决多位数字符串排序错误的问题
• 通过CASE判断区分稳定版和预发布版，符合常规版本优先级规则

方案2：使用semver扩展（推荐） #

PostgreSQL提供semver扩展专门处理语义化版本号，排序逻辑更严谨：

1. 先安装扩展（需超级用户权限）：

CREATE EXTENSION IF NOT EXISTS semver;

2. 执行查询：

SELECT version
FROM packages
ORDER BY
    -- 将版本号转换为semver类型，自动处理版本排序规则
    semver(
        -- 适配非标准的多段补丁号（如3.0.4-1-1转为3.0.4-1.1符合semver规范）
        regexp_replace(version, '-(\d+)-(\d+)', '-\\1.\\2', 'g')
    ) DESC
LIMIT 1;

说明：

• semver扩展严格遵循语义化版本规范，自动处理主版本、次版本、修订版本、预发布版本的排序
• 对非标准的版本格式（如3.0.4-1-1）做简单替换即可适配，排序结果更精准

原查询失效原因 #

原查询通过固定正则截取字符串进行排序：

• 字符串类型的数字排序会出现"10" < "9"的错误（ASCII字符顺序导致）
• 新增字符串后缀后，正则匹配失败返回null，破坏排序逻辑

内容的提问来源于stack exchange，提问作者Nikola