Обсуждение: Error when inserting data

Hi folks!

I just found a strange thing in pg8.0.1. I have a table called
Anwesenheit with an int4-field called TeilnehmerID, wich is constrained
to another tables primary key.
Now when I try to insert data into the table, pg throws an error. It
looks like that:

query: INSERT INTO "Anwesenheit" ("TeilnehmerID", "Monat", "Jahr")
VALUES (-1324054016, 9, 2003);

errorlog:
ERROR: 22003: integer out of range
LOCATION:  int84,  int8.c:947
STATEMENT:  INSERT INTO "Anwesenheit" ("TeilnehmerID", "Monat", "Jahr")
VALUES (-1324054016, 9, 2003);

The interesting part of the table-definition is
CREATE TABLE "Anwesenheit"
(
  "lID" int4 NOT NULL DEFAULT nextval('Anwesenheit_s'::text),
  "TeilnehmerID" int4 DEFAULT 0,
  "Monat" int4 DEFAULT date_part('month'::text, (now())::timestamp
without time zone),
  "Jahr" int4 DEFAULT date_part('year'::text, (now())::timestamp without
time zone),
[...],
 CONSTRAINT a_pk PRIMARY KEY ("lID"),
  CONSTRAINT fk FOREIGN KEY ("TeilnehmerID") REFERENCES "Teilnehmer"
("ID") ON UPDATE CASCADE ON DELETE CASCADE
)
WITH OIDS;


I'm puzzled, because the integervalue is accepted as int4 by pg:
data=# SELECT (-1324054016)::int4;
    int4
-------------
 -1324054016
(1 row)

I use this insert-statement via odbc in my access-app. The error also
occurs, when I connect directly to the database.

Any ideas are welcome!

Thanks,
Thomas Bache

postgresql@thomasbache.de writes:
> ERROR: 22003: integer out of range
> LOCATION:  int84,  int8.c:947

So this is complaining about something that was initially an int8,
which is to say none of the stuff you've shown us, except possibly
the serial column (could the serial counter have exceeded 2^31?).

> The interesting part of the table-definition is

I think the problem is probably buried in something you didn't
show us, eg a default for one of the other columns.

            regards, tom lane

> So this is complaining about something that was initially an int8,
> which is to say none of the stuff you've shown us, except possibly
> the serial column (could the serial counter have exceeded 2^31?).

Gee, thanks a lot for the hint! The sequence causes this overflow ... I
didn't think of it.

You saved my weekend!

Thanks again,
Thomas Bache