From: Richard Huxton <dev@archonet.com>
To: Daniel Lau <iedaniel@ust.hk>, pgsql-sql@postgresql.org
Subject: Re: Type conversion from TEXT to DOUBLE PRECISION
Date: Fri, 9 Jan 2004 08:09:54 +0000
User-Agent: KMail/1.5
References: <Pine.WNT.3.96L2.1040109152354.256D-100000@iey118.ust.hk>
In-Reply-To: <Pine.WNT.3.96L2.1040109152354.256D-100000@iey118.ust.hk>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200401090809.55024.dev@archonet.com>

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