在条件postgres选择时强制转换数据

Question

我有长表格式的数据，所有数据存储为文本类型。我没有权限在数据库等上添加功能。Pg版本12+。我有很多行，我需要创建和转换为数字，如下面的第一次尝试。

SELECT t1.sid as record_number
, (MAX((t1.data)::numeric) FILTER ( WHERE t1.cid = 112)) AS new_field1_numeric
, (MAX((t1.data)::numeric) FILTER ( WHERE t1.cid = 122))  AS new_field2_numeric
FROM original_data t1 
WHERE t1.nid = 10090
GROUP BY t1.sid

返回错误invalid input syntax for type numeric: ""，因为数据存储为文本，未使用的字段没有内容。

我试过了:

(MAX((t1.data)::numeric) FILTER ( WHERE (t1.cid = 122) and (t1.data != '') ))  AS new_field2_numeric

返回相同的错误

和

(MAX (CASE WHEN ((t1.cid=122)) then ((regexp_replace(t1.data, 'D','','g'), '')::numeric) else 0::numeric end) ) as test_numeric

返回ERROR: cannot cast type record to numeric

Answer 1

在我的例子中，字段稍后将被求和，并且只有正数值是可能的，答案是

(MAX(CASE
WHEN t1.cid = 122 AND t1.data != '' THEN t1.data::numeric
ELSE 0::numeric 
END))AS field2_numeric

如果可以为空值，则变为

(MAX(CASE
WHEN t1.cid = 122 AND t1.data ISNULL THEN NULL
WHEN t1.cid = 122 AND t1.data != '' THEN t1.data::numeric
ELSE 0::numeric 
END))AS field2_numeric