PostgreSQL修改列类型为semver遇错误，如何兼容无效值？

我有一张名为relations的表，列结构如下：

system_name varchar(255),
from_package_name varchar(255),
from_version varchar(255)

其中from_version列存储软件包版本号，本应符合语义化版本（Semantic Versioning）规范，但实际存在不符合规范的值。尝试执行以下SQL修改该列类型为PostgreSQL的semver扩展类型时：

ALTER TABLE relations
ALTER COLUMN from_version TYPE SEMVER
USING CAST((COALESCE(from_version,NULL)) AS SEMVER);

触发错误：

ERROR:  bad semver value '2.16.0-27020175.00aeff62': semver prerelease numbers can't start with 0 

SQL state: XX000

需要将无法转换为semver类型的值替换为NULL，完成列类型修改。

利用PostgreSQL semver扩展提供的semver_valid(text)函数，判断字符串是否为有效的语义化版本号，仅对有效值进行类型转换，无效值设为NULL。修改后的SQL语句如下：

ALTER TABLE relations
ALTER COLUMN from_version TYPE SEMVER
USING CASE 
         WHEN semver_valid(from_version) THEN from_version::SEMVER
         ELSE NULL 
      END;

说明 #

• semver_valid()函数会检查输入字符串是否符合semver规范，返回布尔值；
• 对于不符合规范的版本号（如示例中的2.16.0-27020175.00aeff62，因预发布段的数字以0开头违反规范），会被替换为NULL，避免转换错误；
• 执行该语句前需确保已安装semver扩展（若未安装，可先执行CREATE EXTENSION IF NOT EXISTS semver;）。

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