casting - PostgreSQL : to_number() function -

casting - PostgreSQL : to_number() function -

i need cast character varying column numeric type. source column contains values : 9.9 ; 99.9 ; null. utilize next sql instruction :

alter table test alter column pression type numeric using to_number(pression, '99.9') ;

i error : « invalid input syntax type numeric: "" » (sql : 22p02)...

is there problem having both 9.9 , 99.9 kinds of values ?

thomas

it possible cast null numeric:

select null::numeric; numeric --------- (1 row) select to_number(null, '99.9'); to_number ----------- (1 row)

but not empty string:

select ''::numeric; error: invalid input syntax type numeric: "" line 1: select ''::numeric;

so update column null before altering it

update test set pression = null pression = '';

then alter it

alter table test alter column pression type numeric using to_number(pression, '99.9') ;

postgresql casting