Re: Problem while inserting a varchar

Christian Stalp <christian.stalp@gmx.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