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