| From: | Kevin Brannen <kevinb(at)nurseamerica(dot)net> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | serial columns & loads misfeature? |
| Date: | 2002-06-28 18:28:20 |
| Message-ID: | 3D1CAAC4.2060202@nurseamerica.net |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
I'm new to Postgres, so sorry if this is easy, but I did search the docs
and could find nothing to answer this...
I've got a Mysql DB that I've dumped out and am trying to insert into a
Pg DB, as we transition. There were a few changes I had to do to the
data, but they were easy--except for the auto_increment columns. :-/
After I created the DB, I inserted the data (thousands of inserts) via
psql. All went well. Then I started testing the changed code (Perl)
and when I went to insert, I got a "dup key" error.
It took me awhile to figure out what was going on, but I can recreate
the problem with:
create table test (s serial, i int);
insert into test values (1,1);
insert into test values (2,2);
insert into test values (3,3);
insert into test (i) values (4);
ERROR: Cannot insert a duplicate key into unique index test_s_key
I was expecting the system to realize new "keys" had been inserted, and
so when the "nextval" that implicitly happens on a serial field is run,
it would "know" that it was too small and return "max(s)+1". [FWIW, my
expectations in this area were set by my experience with Informix and
mysql, both do this; not sure if other RDBMs do.]
I realize I can fix this by writting a Perl/DBI script to read the list
of sequences, and do [conceptually]:
get the list of sequences
from the sequence name, retrieve the table name
n=select max(id)+1 from table;
select setval('seq_name',n) from seq_name;
and things will be fine from here after, but surely this is a common
enough problem after a bulk load that there is something already built
in to handle this and I just don't have it configured correctly (or is
this a bug?).
Oh, this on a RH 7.2 system with Pg 7.1.3.
TIA for any help in understanding this better!
Kevin
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Bruce Momjian | 2002-06-28 18:45:34 | Re: Shared Memory Sizing |
| Previous Message | Thomas Beutin | 2002-06-28 17:43:31 | Re: One source of constant annoyance identified |