Error: invalid input syntax for type numeric: “” (postgresql)

Question

I'm trying to alter the column data type in my postgresql table. The column name is _2010_10, type is text, and the value is 18.74 (in text format). I'm trying to change the text type to numeric. This is my input/output:

ALTER table cadata.pricetorentratio 
ALTER column _2010_10 type numeric USING (trim(_2010_10)::numeric);

ERROR: invalid input syntax for type numeric: ""

Not sure why I'm getting this error.

Answer 1

You could use NULLIF to handle blank string '':

ALTER table pricetorentratio 
ALTER column _2010_10 type numeric USING (NULLIF(trim(_2010_10),'')::numeric);

DBFiddle Demo

---

SELECT ''::numeric
-- invalid input syntax for type numeric: ""

Answer 2

ALTER TABLE `pricetorentratio` CHANGE `_2010_10` `_2010_10` FLOAT NULL DEFAULT NULL;