将字符串转换为数字，将空值或空字符串解释为0。

Question

我有一个Postgres表格，其中包含一个字符串列，其携带数字值。我需要将这些字符串转换为数字进行数学计算，但我需要将NULL值以及空字符串都解释为0。

我可以将空字符串转换为空值：

# select nullif('','');
 nullif 
--------

(1 row)

我可以将空值转换为0：

# select coalesce(NULL,0);
 coalesce 
----------
        0
(1 row)

我可以把字符串转换成数字：

# select cast('3' as float);
 float8 
--------
      3
(1 row)

但是当我尝试结合这些技术时，我遇到了错误：

# select cast( nullif( coalesce('',0), '') as float);
ERROR:  invalid input syntax for integer: ""
LINE 1: select cast( nullif( coalesce('',0), '') as float);

# select coalesce(nullif('3',''),4) as hi;
ERROR:  COALESCE types text and integer cannot be matched
LINE 1: select coalesce(nullif('3',''),4) as hi;

我做错了什么？

- Phrogz

---

附注：在大多数情况下最好使用“numeric”而不是“float”。仅在确实需要“float”时才使用它。 - Ihor Romanchenko

Answer 1

值的类型需要保持一致；将空字符串合并为0意味着您无法将其与 null 在 nullif 中进行比较。因此，以下任何一种方法都可以：

# create table tests (orig varchar);
CREATE TABLE

# insert into tests (orig) values ('1'), (''), (NULL), ('0');
INSERT 0 4


# select orig, cast(coalesce(nullif(orig,''),'0') as float) as result from tests;
 orig | result 
------+--------
    1 |      1
      |      0
      |      0
    0 |      0
(4 rows)


# select orig, coalesce(cast(nullif(orig,'') as float),0) as result from tests;
 orig | result 
------+--------
 1    |      1
      |      0
      |      0
 0    |      0
(4 rows)

- Phrogz

Answer 2

你也可以使用

cast(
    case
        when coalesce(orig, '') = '' then '0'
        else orig
    end
    as float
)

既然你已经表述得相当详细了，你也可以稍微拆开来说：

cast(
    case
        when orig is null then '0'
        when orig = '' then '0'
        else orig
    end
    as float
)

或者你可以把 cast 放在 CASE 语句里：

case
    when coalesce(orig, '') = '' then 0.0
    else cast(orig as float)
end

使用CASE语句可以更容易地考虑其他特殊条件，这在我看来似乎也更清晰地表达了逻辑。另一方面，这也涉及到个人喜好等问题。

- mu is too short

Answer 3

实际上，你可以将 NULL 转换为 int 类型，但是你无法将空字符串转换为 int。假设你想在 data1 包含空字符串或 NULL 的情况下在新列中得到 NULL，则可以像这样操作：

UPDATE table SET data2 = cast(nullif(data1, '') AS int);

或者

UPDATE table SET data2 = nullif(data1, '')::int;

参考资料

- florentpousserot

---

从第二句开始：“[…] 我需要将 NULL 值和空字符串都解释为 0。” - Phrogz

Answer 4

检查查询参数是否为空（接受null、空字符串或值）：

SELECT CAST(TO_JSON(NULLIF(:myParameter, NULL)) AS VARCHAR) IS NULL OR
   CAST(TO_JSON(NULLIF(:myParameter, NULL)) AS VARCHAR) IN ('""');

- Zon