PostgreSQL中ResultSetMetaData无法识别整数字段类型问题

我需要从PostgreSQL数据库返回的结果集生成Excel文档，要求区分decimal（double）和非decimal（integer）数值类型。在SQL语句中，我通过CASE语句将totalUnits字段在非百分比场景下转换为bigint，但用ResultSetMetaData检测时，该字段仍显示为NUMERIC类型。试了两种显式转换方式结果一致，但skunumber字段的varchar转换却能被正确识别。

SQL语句片段 #

...
 (select product, reportflag,
                        (select measure_name from report_measures rm where rm.measure_key = typeflag and rm.object_id=(select object_id from reportdet)) as salesLoss,
                        (case when skunumber = 'MULTIPLE' then '-1' else skunumber end)::varchar(12) as skunumber, skuname, null::varchar(12) as substitutesku,
                        null as substituteskuname,
                        null::decimal as substituteTransferSales,
                        case when measure_symbol[3] = '%' then ((measures ->> measure_key[3])::decimal)/100 else (measures ->> measure_key[3])::decimal end as totalSpend, 
                        case when measure_symbol[4] = '%' then ((measures ->> measure_key[4])::decimal)/100 else (measures ->> measure_key[4])::bigint end as totalUnits,  -- 期望此处返回integer类型
                        case when measure_symbol[5] = '%' then ((measures ->> measure_key[5])::decimal)/100 else (measures ->> measure_key[5])::decimal end as totalVisits,
...

Java检测代码片段 #

public void processRow(ResultSet rs) throws SQLException {
    var rsmd = rs.getMetaData();
    ...
}

检测结果显示totalunits字段类型为NUMERIC，而非预期的bigint。

解答 #

问题出在PostgreSQL的CASE表达式类型推导规则上：CASE的所有分支必须返回兼容且优先级最高的类型。

你的totalUnits字段CASE语句里，两个分支的返回类型分别是：

• 百分比分支：((measures ->> measure_key[4])::decimal)/100，结果是DECIMAL类型
• 非百分比分支：(measures ->> measure_key[4])::bigint，结果是BIGINT类型

PostgreSQL会自动将低优先级的BIGINT隐式转换为更高优先级的DECIMAL，最终整个CASE表达式的返回类型就是DECIMAL（PostgreSQL中DECIMAL和NUMERIC是等价类型），所以ResultSetMetaData检测到的是NUMERIC。

而skunumber的CASE分支返回的都是VARCHAR类型（'-1'是字符串，else分支的skunumber也被转换为varchar），类型完全一致，所以能被正确识别。

解决方法 #

要让整个CASE表达式返回整数类型，需要统一两个分支的返回类型：

1. 如果百分比场景下的数值可以接受截断小数（比如直接转为整数）：

case 
    when measure_symbol[4] = '%' then ((measures ->> measure_key[4])::decimal)::bigint  -- 舍弃小数部分转BIGINT
    else (measures ->> measure_key[4])::bigint 
end as totalUnits

2. 如果需要保留百分比的小数精度，同时在Excel中区分类型：
   • 新增一个标识字段（比如is_percentage），标记该字段是否为百分比类型，在Java代码中根据标识设置Excel单元格格式
   • 或者将百分比分支的结果转为整数等价形式（比如乘以100后转BIGINT），后续在Excel中再转换为百分比格式

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