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

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

I can 将空字符串转换为空值 https://stackoverflow.com/a/14035890/405017:

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

(1 row)

我可以将空值转换为0 https://stackoverflow.com/a/7452522/405017:

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

我可以将字符串转换为数字 https://stackoverflow.com/a/10518415/405017:

# 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;

我究竟做错了什么？

值的类型需要一致；将空字符串合并为 0 意味着您无法将其与null in the 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)