Thread: Problem while inserting a varchar

Hello togehter,
I have a problem while writing a string look like this: '0:40:f4:d3:0:0'
into a table.
My table has this structure:
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? Is that wrong? How can I
create a autogenerated id and how should format an input string that
adapts to that  demand ? I think the real question should be "is
bigserial really auto-generated number?

Thank you

Gruss Christian

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

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

Hello, thank you Tom Lane, thank you Michael Glaesemann,

Tom Lane wrote:
>
>
> 1. Explicitly say you want the default for the first column:
>
> INSERT INTO RETRY VALUES ( DEFAULT, '0:40:f4:d3:0:0', ... );
>
That seems to work. I allready omited rid, I think this was the problem.

Now I write (trough my program): INSERT INTO RETRY VALUES ( DEFAULT,
'0:40:f4:d3:0:0','0:40:f4:d3:0:0',1, 2008-02-17,20:34:00 );

But now I get an syntax-error at character 87 »:«. I think that has
nothing to do with my first problem.
The datatype here is time. Does the ":" has a special meaning here?
> 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 ...
>
Really? I did not find anything in the documentation about it. That
would be very appropriate.

Gruss Christian