Re: Type conversion from TEXT to DOUBLE PRECISION

On Fri, 9 Jan 2004, Tom Lane wrote:

> Richard Huxton <dev@archonet.com> writes:
> > Since your error seems to be complaining about a space, I'd guess you've got 
> > other than numeric values in _aaa.
> 
> In fact, with a bit of experimentation I see the same error message:
> 
> regression=# select to_number('12345', '99999');
>  to_number
> -----------
>      12345
> (1 row)
> 
> regression=# select to_number('1234 ', '99999');
>  to_number
> -----------
>       1234
> (1 row)
> 
> regression=# select to_number(' 1234', '99999');
>  to_number
> -----------
>       1234
> (1 row)
> 
> regression=# select to_number('     ', '99999');
> ERROR:  invalid input syntax for type numeric: " "
> regression=# select to_number('zzzzz', '99999');
> ERROR:  invalid input syntax for type numeric: " "
> regression=#
> 
> The error message's report of the input string seems a tad misleading,
> especially in the last case.  (Karel, is this fixable?)  But anyway,
> it sure looks like the problem is bad input data.
> 
>             regards, tom lane
> 

Thanks Tom and Richard. Yes, it is the problem of bad input data. I have
4000 rows of data and there are 10 rows containing blank string ('
'). I have to add a Where clause to carry out the SQL:

UPDATE _table
SET _bbb = to_number(substring(_aaa from 1 for 5), '99999')
WHERE _aaa <> '     ';

I guess a function checking if a string contains only numbers would be
betteroff. But I find no such functions. Checking that it's not blank
would be the only solution I can think of.

Thanks again.

regards,
Daniel Lau