PostgreSQL : to_number() function

Question

I need to cast a CHARACTER VARYING column into a NUMERIC type. The source column contains values like : 9.9 ; 99.9 ; NULL. I use the following SQL instruction :

ALTER TABLE test ALTER COLUMN pression TYPE NUMERIC USING to_number(pression, '99.9') ;

I get an error : « invalid input syntax for type numeric: "" » (SQL : 22P02)...

Is there a problem with having both 9.9 and 99.9 kinds of values ?

Thomas

In fact, it seems that I have empty cells in my column... I have found a solution by updating the column and giving any NULL cells a default value. Then, the casting is possible to NUMERIC. I finally have to update my column again to set the NULL value instead of the default... — wiltomap
– wiltomap, Commented Jun 19, 2014 at 12:54
Thanks for the link I had read before posting. In my case, I do not use a SELECT but an ALTER TABLE so I can't use CAST() for exemple... — wiltomap
– wiltomap, Commented Jun 19, 2014 at 13:14
You can use any expression in the USING clause, including cast, nullif() and to_number() calls. — pozs
– pozs, Commented Jun 19, 2014 at 13:49

Clodoaldo Neto · Accepted Answer · 2014-06-19 13:40:04Z

0

It is possible to cast a null to numeric:

select null::numeric;
 numeric 
---------

(1 row)

select to_number(null, '99.9');
 to_number 
-----------

(1 row)

But not an empty string:

select ''::numeric;
ERROR:  invalid input syntax for type numeric: ""
LINE 1: select ''::numeric;

So update the column to null before altering it

update test
set pression = null
where pression = '';

Then alter it

ALTER TABLE test 
ALTER COLUMN pression TYPE NUMERIC USING to_number(pression, '99.9') ;

edited Jun 19, 2014 at 13:40

answered Jun 19, 2014 at 13:29

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

1 Comment

wiltomap Over a year ago

That's it ! The problem comes from cells containing ''. By updating them to NULL value, the casting works well. Thank you for this is probably the simpliest solution !

Collectives™ on Stack Overflow

PostgreSQL : to_number() function

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related