From: Daniel Lau <iedaniel@ust.hk>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org, Karel Zak <zakkr@zf.jcu.cz>
Subject: Re: Type conversion from TEXT to DOUBLE PRECISION
Date: Fri, 9 Jan 2004 23:44:36 +0800 (HKT)
Message-ID: <Pine.GSO.3.95L.1040109233321.6593B-100000@uststf1> (raw)
In-Reply-To: <23739.1073660245@sss.pgh.pa.us>
On Fri, 9 Jan 2004, Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
> > Since your error seems to be complaining about a space, I'd guess you've got
> > other than numeric values in _aaa.
>
> In fact, with a bit of experimentation I see the same error message:
>
> regression=# select to_number('12345', '99999');
> to_number
> -----------
> 12345
> (1 row)
>
> regression=# select to_number('1234 ', '99999');
> to_number
> -----------
> 1234
> (1 row)
>
> regression=# select to_number(' 1234', '99999');
> to_number
> -----------
> 1234
> (1 row)
>
> regression=# select to_number(' ', '99999');
> ERROR: invalid input syntax for type numeric: " "
> regression=# select to_number('zzzzz', '99999');
> ERROR: invalid input syntax for type numeric: " "
> regression=#
>
> The error message's report of the input string seems a tad misleading,
> especially in the last case. (Karel, is this fixable?) But anyway,
> it sure looks like the problem is bad input data.
>
> regards, tom lane
>
Thanks Tom and Richard. Yes, it is the problem of bad input data. I have
4000 rows of data and there are 10 rows containing blank string ('
'). I have to add a Where clause to carry out the SQL:
UPDATE _table
SET _bbb = to_number(substring(_aaa from 1 for 5), '99999')
WHERE _aaa <> ' ';
I guess a function checking if a string contains only numbers would be
betteroff. But I find no such functions. Checking that it's not blank
would be the only solution I can think of.
Thanks again.
regards,
Daniel Lau
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: iedaniel@ust.hk, tgl@sss.pgh.pa.us, dev@archonet.com, zakkr@zf.jcu.cz
Subject: Re: Type conversion from TEXT to DOUBLE PRECISION
In-Reply-To: <Pine.GSO.3.95L.1040109233321.6593B-100000@uststf1>
* 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