X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 18A6D9FA4AA for ; Wed, 19 Jul 2006 22:25:03 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 11031-09 for ; Wed, 19 Jul 2006 22:24:58 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.175]) by postgresql.org (Postfix) with ESMTP id DE3D99FA4A7 for ; Wed, 19 Jul 2006 22:24:58 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id u2so619355uge for ; Wed, 19 Jul 2006 18:24:56 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=jt/SJSjLNnRd8Z1MD2no0dYx/R+2sidOKTmAA3JDLUeH2YqM+AJ0iIcI5Sqc4cvzT3mDPb7vAwYlUpH6np/21xyURRqG+0opNSJ4KzXnCBZcX78mWR+WH57WA5Kc/H2VElL+6V5u7/i4rj3PmiYlUtCX2amn5B1kerLdfZ+2cNA= Received: by 10.78.160.2 with SMTP id i2mr45937hue; Wed, 19 Jul 2006 18:24:56 -0700 (PDT) Received: by 10.78.32.10 with HTTP; Wed, 19 Jul 2006 18:24:56 -0700 (PDT) Message-ID: Date: Wed, 19 Jul 2006 20:24:56 -0500 From: "Aaron Bono" To: "Ghislain Bob Hachey" Subject: Re: Insert NULL value with to_numeric() Cc: pgsql-admin@postgresql.org In-Reply-To: <1153356715.2234.342.camel@informationmanagement.pfnet.local> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_55509_32920359.1153358696814" References: <1153356715.2234.342.camel@informationmanagement.pfnet.local> X-Google-Sender-Auth: d250975cb5bd18fe X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/160 X-Sequence-Number: 22406 ------=_Part_55509_32920359.1153358696814 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/19/06, Ghislain Bob Hachey wrote: > > Hello all, > > I'm a newbie. PostgreSQL 8.1.4. Fedora Core 5. > > I'm writing a small java application that will import CSV txt files into > my DB. The SQL statement I'm sending to pgsql looks like this: > > "INSERT into table (col1,col2,col3) values (to_number(?, > '999'),to_timestamp(?, 'MM/DD/YYYY HH24:MI:SS'),to_number(?, '9999'); > > Values are taken from a String array. > > sometimes in col3 (smallint in pgsql DB) I need to insert a NULL value > but when I do I get the following error message: > > invalid input syntax for type numeric: " " > > I've search the archives and Internet and found similar situations but I > guess not experienced enough to fix this myself with info that I've > found. Why use to_number or to_timestamp? I assume you are using setString in your prepared statement. In your Java code you can use setNull if the value is null and setInt or setLong or setTimestamp if it is not. Then you don't need the to_number or to_timestamp. Something like this: Integer value1; Date value2; Integer value3; PreparedStatement stmt = conn.prepareStatement( "INSERT INTO table (col1,col2,col3) " + "VALUES (?, ?, ?)" ); int index = 1; stmt.setInt(index++, value1.intValue()); stmt.setTimestamp(index++, new java.sql.Timestamp(value2.getTime())); if (value3 == null) { stmt.setNull(index++, Types.INTEGER); } else { stmt.setInt(index++, value3.intValue()); } ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com ================================================================== ------=_Part_55509_32920359.1153358696814 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/19/06, Ghislain Bob Hachey <ghachey@pipolfastaem.gov.sb> wrote:
Hello all,

I'm a newbie.  PostgreSQL 8.1.4. Fedora Core 5.

I'm writing a small java application that will import CSV txt files into
my DB.  The SQL statement I'm sending to pgsql looks like this:

"INSERT into table (col1,col2,col3) values (to_number(?,
'999'),to_timestamp(?, 'MM/DD/YYYY HH24:MI:SS'),to_number(?, '9999');

Values are taken from a String array.

sometimes in col3 (smallint in pgsql DB) I need to insert a NULL value
but when I do I get the following error message:

invalid input syntax for type numeric: " "

I've search the archives and Internet and found similar situations but I
guess not experienced enough to fix this myself with info that I've
found.

 
Why use to_number or to_timestamp?  I assume you are using setString in your prepared statement.  In your Java code you can use setNull if the value is null and setInt or setLong or setTimestamp if it is not.  Then you don't need the to_number or to_timestamp.

Something like this:

Integer value1;
Date value2;
Integer value3;
PreparedStatement stmt = conn.prepareStatement(
   "INSERT INTO table (col1,col2,col3) " +
   "VALUES (?, ?, ?)"
);
int index = 1;
stmt.setInt(index++, value1.intValue());
stmt.setTimestamp(index++, new java.sql.Timestamp(value2.getTime()));
if (value3 == null) {
   stmt.setNull(index++, Types.INTEGER);
} else {
   stmt.setInt(index++, value3.intValue());
}

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
================================================================== ------=_Part_55509_32920359.1153358696814--