X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A71F1D1B46C for ; Fri, 9 Jan 2004 16:42:25 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 29890-09 for ; Fri, 9 Jan 2004 12:41:54 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 0BFEFD1DBD5 for ; Fri, 9 Jan 2004 10:57:21 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id i09EvP19023740; Fri, 9 Jan 2004 09:57:25 -0500 (EST) To: Richard Huxton Cc: Daniel Lau , pgsql-sql@postgresql.org, Karel Zak Subject: Re: Type conversion from TEXT to DOUBLE PRECISION In-reply-to: <200401090809.55024.dev@archonet.com> References: <200401090809.55024.dev@archonet.com> Comments: In-reply-to Richard Huxton message dated "Fri, 09 Jan 2004 08:09:54 +0000" Date: Fri, 09 Jan 2004 09:57:25 -0500 Message-ID: <23739.1073660245@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/71 X-Sequence-Number: 16464 Richard Huxton 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