From: Tom Lane <tgl@sss.pgh.pa.us>
To: Daniel Lau <iedaniel@ust.hk>
Cc: pgsql-sql@postgresql.org
Subject: Re: Type conversion from TEXT to DOUBLE PRECISION
Date: Fri, 09 Jan 2004 02:47:50 -0500
Message-ID: <21666.1073634470@sss.pgh.pa.us> (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>
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
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: tgl@sss.pgh.pa.us, iedaniel@ust.hk
Subject: Re: Type conversion from TEXT to DOUBLE PRECISION
In-Reply-To: <21666.1073634470@sss.pgh.pa.us>
* 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