Type conversion from TEXT to DOUBLE PRECISION - Mailing list pgsql-sql

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

Here is the table, named: _table:

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: " "

I guess the machine can not treat TEXT as CHAR[5]. I tries to CAST TEXT as
CHAR[5]. It also doesnt allow me to do so.

Can anyone give me some hints on this?

regards,
Daniel