pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedType conversion from TEXT to DOUBLE PRECISION
5+ messages / 3 participants
[nested] [flat]
* Type conversion from TEXT to DOUBLE PRECISION
@ 2004-01-09 07:35 Daniel Lau <iedaniel@ust.hk>
0 siblings, 2 replies; 5+ messages in thread
From: Daniel Lau @ 2004-01-09 07:35 UTC (permalink / raw)
To: pgsql-sql
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
Here is the table, named: _table:
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: " "
I guess the machine can not treat TEXT as CHAR[5]. I tries to CAST TEXT as
CHAR[5]. It also doesnt allow me to do so.
Can anyone give me some hints on this?
regards,
Daniel
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Type conversion from TEXT to DOUBLE PRECISION
@ 2004-01-09 07:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Daniel Lau <iedaniel@ust.hk>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2004-01-09 07:47 UTC (permalink / raw)
To: Daniel Lau <iedaniel@ust.hk>; +Cc: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Type conversion from TEXT to DOUBLE PRECISION
@ 2004-01-09 08:09 Richard Huxton <dev@archonet.com>
parent: Daniel Lau <iedaniel@ust.hk>
1 sibling, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2004-01-09 08:09 UTC (permalink / raw)
To: Daniel Lau <iedaniel@ust.hk>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Type conversion from TEXT to DOUBLE PRECISION
@ 2004-01-09 14:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2004-01-09 14:57 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Daniel Lau <iedaniel@ust.hk>; pgsql-sql@postgresql.org, Karel Zak <zakkr@zf.jcu.cz>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Type conversion from TEXT to DOUBLE PRECISION
@ 2004-01-09 15:44 Daniel Lau <iedaniel@ust.hk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Daniel Lau @ 2004-01-09 15:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql@postgresql.org, Karel Zak <zakkr@zf.jcu.cz>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-01-09 15:44 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-09 07:35 Type conversion from TEXT to DOUBLE PRECISION Daniel Lau <iedaniel@ust.hk>
2004-01-09 07:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-09 08:09 ` Richard Huxton <dev@archonet.com>
2004-01-09 14:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-09 15:44 ` Daniel Lau <iedaniel@ust.hk>
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