3

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

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
wiltomap
  • 3,933
  • 8
  • 37
  • 54
  • 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 Jun 19 '14 at 12:54
  • see this: http://stackoverflow.com/questions/10518258/typecast-string-to-integer-postgres – Molegrammer Jun 19 '14 at 12:58
  • 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 Jun 19 '14 at 13:14
  • You can use any expression in the `USING` clause, including `cast`, `nullif()` and `to_number()` calls. – pozs Jun 19 '14 at 13:49

1 Answers1

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') ;
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • 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 ! – wiltomap Jun 19 '14 at 14:04