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.

Lukasz Szozda · Accepted Answer · 2018-04-18 17:24:04Z

5

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: ""

answered Apr 18, 2018 at 17:24

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Iqbal · Accepted Answer · 2018-04-18 20:34:46Z

-1

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

edited Apr 18, 2018 at 20:34

user330315

answered Apr 18, 2018 at 17:29

Iqbal

251 silver badge9 bronze badges

Collectives™ on Stack Overflow

Error: invalid input syntax for type numeric: "" (postgresql)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related