Cast string to number, interpreting null or empty string as 0

Question

I have a Postgres table with a string column carrying numeric values. I need to convert these strings to numbers for math, but I need both NULL values as well as empty strings to be interpreted as 0.

I can convert empty strings into null values:

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

(1 row)

And I can convert null values into a 0:

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

And I can convert strings into numbers:

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

But when I try to combine these techniques, I get errors:

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

What am I doing wrong?

Answer 1

The types of values need to be consistent; coalescing the empty string to a 0 means that you cannot then compare it to null in the nullif. So either of these works:

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

Answer 2

You could also use

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

You could also unwrap that a bit since you're being fairly verbose anyway:

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

or you could put the cast inside the CASE:

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

A CASE makes it a bit easier to account for any other special conditions, this also seems like a clearer expression of the logic IMO. OTOH, personal taste and all that.

Answer 3

Actually, you can cast NULL to int, you just can't cast an empty string to int. Assuming you want NULL in the new column if data1 contains an empty string or NULL, you can do something like this:

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

or

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

Reference