Cast string to number, interpreting null or empty string as 0
Solution 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)
Solution 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.
Solution 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;
Related videos on Youtube
Phrogz
I like Ruby, Lua, Io, JavaScript…and my real-life children and wife :) "It is better to illuminate than merely to shine; to deliver to others contemplated truths than merely to contemplate." ~Thomas Aquinas
Updated on July 09, 2022Comments
-
Phrogz almost 2 years
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 as0
.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?
-
Ihor Romanchenko over 10 yearsSide note - it is better to use
numeric
instead offloat
in most cases. Usefloat
only when you know you really needfloat
.
-
-
Phrogz over 10 yearsFrom the second sentence: "[…] I need both NULL values as well as empty strings to be interpreted as 0."