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

Re: Problem while inserting a varchar

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
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:50
Message-ID: 26959.1203276170@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Christian Stalp <christian(dot)stalp(at)gmx(dot)de> writes:
> CREATE  TABLE RETRY(
>     rid bigserial primary key,
>     source_macaddress varchar (40),
>     destination_macaddress varchar (40),
>     accesspoint integer references ACCESSPOINT(aid),
>     retray_day date,
>     retry_time time
> );

> 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 first value we have is a bigserial. I thought this is integer-number 
> which is automatically created by the database?

No, not if you explicitly specify a value for it.  Serial columns just
have a useful default.  If it weren't that way, how could you dump and
reload the table?

You have several options:

1. Explicitly say you want the default for the first column:

INSERT INTO RETRY VALUES ( DEFAULT, '0:40:f4:d3:0:0', ... );

2. Use a column name list, and omit "rid" from it:

INSERT INTO RETRY (source_macaddress, destination_macaddress, ...)
  VALUES ( '0:40:f4:d3:0:0', ... );

3. Put the serial column last, so that you can just omit it without
saying anything.  (This last is not strictly according to the letter of
the SQL spec, I think, but PG allows it and so do some other databases.)

BTW, why don't you use the "macaddr" data type for those MAC address
columns?  Then you'd get some error checking ...

			regards, tom lane

In response to

Responses

pgsql-novice by date

Next:From: Christian StalpDate: 2008-02-17 19:54:49
Subject: Re: Problem while inserting a varchar
Previous:From: Michael GlaesemannDate: 2008-02-17 19:22:09
Subject: Re: Problem while inserting a varchar

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