serial columns & loads misfeature?
От | Kevin Brannen |
---|---|
Тема | serial columns & loads misfeature? |
Дата | |
Msg-id | 3D1CAAC4.2060202@nurseamerica.net обсуждение исходный текст |
Ответы |
Re: serial columns & loads misfeature?
Re: serial columns & loads misfeature? Re: serial columns & loads misfeature? Re: serial columns & loads misfeature? |
Список | 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
В списке pgsql-general по дате отправления: