如何将文本列转为另一列指定类型并按数值类型筛选配置表记录？

1. 将val列文本转换为val_en指定的数据类型 #

在PostgreSQL中，可以通过CASE语句匹配val_en的类型标识，对val列的文本值做对应的数据类型强制转换。如果需要兼容格式不符合目标类型的场景（避免转换失败直接报错），可以用TRY_CAST替代直接强制转换，转换失败时会返回NULL而非抛出错误。

基础转换示例 #

SELECT 
    id,
    name,
    val,
    val_en,
    -- 根据val_en指定的类型转换val值
    CASE val_en
        WHEN 'integer' THEN val::integer
        WHEN 'double' THEN val::double precision
        WHEN 'boolean' THEN val::boolean
        WHEN 'date' THEN val::date
        ELSE val::text
    END AS converted_val
FROM conf;

容错转换（兼容格式错误场景） #

SELECT 
    id,
    name,
    val,
    val_en,
    CASE val_en
        WHEN 'integer' THEN TRY_CAST(val AS integer)
        WHEN 'double' THEN TRY_CAST(val AS double precision)
        WHEN 'boolean' THEN TRY_CAST(val AS boolean)
        WHEN 'date' THEN TRY_CAST(val AS date)
        ELSE val::text
    END AS converted_val
FROM conf;

2. 基于转换后的数值类型进行比较筛选 #

直接在WHERE子句中先匹配val_en的类型，再对转换后的值做条件判断即可，这样能避免对不相关类型做无效转换，减少报错风险。

示例：筛选name为'weight'且转换后值大于70的记录 #

SELECT *
FROM conf
WHERE 
    name = 'weight'
    AND val_en = 'double' -- 先限定类型，避免无效转换
    AND val::double precision > 70;

多类型通用筛选示例 #

如果需要同时对多种类型的记录做筛选，可在WHERE中用CASE分支处理：

SELECT *
FROM conf
WHERE 
    CASE val_en
        WHEN 'integer' THEN val::integer > 30 -- 筛选age大于30的记录
        WHEN 'double' THEN val::double precision > 70 -- 筛选weight大于70的记录
        WHEN 'boolean' THEN val::boolean = true -- 筛选male为true的记录
        ELSE false -- 其他类型不满足筛选条件
    END;

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