Skip site navigation (1) Skip section navigation (2)

Re: Problem while inserting a varchar

From: Michael Glaesemann <grzm(at)seespotcode(dot)net>
To: christian(dot)stalp(at)gmx(dot)de
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Problem while inserting a varchar
Date: 2008-02-17 19:22:09
Message-ID: 977C991E-0060-4FD6-9739-F5487211DB4F@seespotcode.net (view raw or flat)
Thread:
Lists: pgsql-novice
On Feb 17, 2008, at 13:30 , Christian Stalp wrote:

> My insert string looks as follows:  INSERT INTO RETRY VALUES  
> ( '0:40:f4:d3:0:0','0:40:f4:d3:0:0',1, 2008-02-17,18:42:05 );
>
> and the postgres-log tells me anything about a invalid input syntax  
> for the whole number »0:40:f4:d3:0:0«

The basic problem is that you're not explicitly listing your insert  
columns. Postgres is interpreting this:

> INSERT INTO RETRY VALUES ( '0:40:f4:d3:0:0','0:40:f4:d3:0:0',1,  
> 2008-02-17,18:42:05 );

as

INSERT INTO RETRY
   (rid, source_macaddress, destination_macaddress, accesspoint,  
retray_day)
   VALUES ( '0:40:f4:d3:0:0','0:40:f4:d3:0:0',1,  
'2008-02-17','18:42:05' );

(btw, the insert you provide won't actually work, as you're not  
quoting your date and time data. It's much better to supply actual  
queries, data, and error messages so we can really see what's  
happening.)

What you're trying to do is

INSERT INTO RETRY
   (source_macaddress, destination_macaddress, accesspoint, retray_day)
   VALUES ( '0:40:f4:d3:0:0','0:40:f4:d3:0:0',1,  
'2008-02-17','18:42:05');

or

INSERT INTO RETRY  VALUES (DEFAULT, '0:40:f4:d3:0:0','0:40:f4:d3:0:0', 
1, '2008-02-17','18:42:05');

In general, it's good to explicitly list the insert columns to  
prevent exactly this issue.

Btw, why are you using separate date and time fields, rather than  
using a timestamp?

Michael Glaesemann
grzm seespotcode net



In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2008-02-17 19:22:50
Subject: Re: Problem while inserting a varchar
Previous:From: Christian StalpDate: 2008-02-17 18:30:48
Subject: Problem while inserting a varchar

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group