Re: Type conversion from TEXT to DOUBLE PRECISION

On Friday 09 January 2004 07:35, Daniel Lau wrote:
> Hi all,
>
> Thank you for reading this mail.
>
> I am trying to do the following:
> Extract the first half of _aaa and put it in column _bbb

> Varchar[10]   |   Double Precision
> _aaa            _bbb
> 1234567890

> I used two functions to do it: substring() and to_number(). The SQL is
> like this:
> UPDATE _table SET _bbb = to_number(substring(_aaa from 1 for 5), '99999');
> The machine fails me and said
> ERROR: invalid input syntac for type numeric: " "

Works here.

richardh=# \d foo           Table "richardh.foo"Column |         Type          | Modifiers
--------+-----------------------+-----------_aaa   | character varying(10) |_bbb   | double precision      |

richardh=# UPDATE foo SET _bbb = to_number(substring(_aaa from 1 for 
5),'99999');
UPDATE 1
richardh=# SELECT * FROM foo
richardh-# ;   _aaa    | _bbb
------------+-------1234567890 | 12345
(1 row)


Since your error seems to be complaining about a space, I'd guess you've got 
other than numeric values in _aaa.

-- Richard Huxton