From: Richard Huxton <dev@archonet.com>
To: Daniel Lau <iedaniel@ust.hk>
To: pgsql-sql@postgresql.org
Subject: Re: Type conversion from TEXT to DOUBLE PRECISION
Date: Fri, 9 Jan 2004 08:09:54 +0000
Message-ID: <200401090809.55024.dev@archonet.com> (raw)
In-Reply-To: <Pine.WNT.3.96L2.1040109152354.256D-100000@iey118.ust.hk>
References: <Pine.WNT.3.96L2.1040109152354.256D-100000@iey118.ust.hk>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, iedaniel@ust.hk
Subject: Re: Type conversion from TEXT to DOUBLE PRECISION
In-Reply-To: <200401090809.55024.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox