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

Daniel Lau <iedaniel@ust.hk> writes:
> 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 for me ...

regression=# create table _table (_aaa varchar(10), _bbb double precision);
CREATE TABLE
regression=# insert into _table values('1234567890', null);
INSERT 180987 1
regression=# UPDATE _table SET _bbb = to_number(substring(_aaa from 1 for 5), '99999');
UPDATE 1
regression=# select * from _table;   _aaa    | _bbb
------------+-------1234567890 | 12345
(1 row)

regression=#

I don't think you have accurately described what you did.
        regards, tom lane