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

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

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)

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.