Migration problem - serial fields - Mailing list pgsql-general

Greetings,

I've only been working with PG for a few days, so bear with
me--hopefully this isn't too much of a newbie dumb question!  Also,
while I have a good bit of experience working with different dbs, I
have little formal training and it's possible that I do not do things
"the right way".  A side-effect of doing small web/db projects mostly
by myself...just some background info for you.

Onward...I've got an existing db in SQL7 that I need to migrate over
to PG.  Following advice from an older usenet post, I was able to
establish an odbc connection between the two servers using Enterprise
Manager.  First I ran create table scripts to create the tables on PG
vs. allowing EM to do it (it did not do a very good job!)  I would
like to use the datasource-to-datasource transfer if possible since it
means I wouldn't have to export to text files and import into PG.

The snag I'm encountering is with Identity fields in SQL7.  I
discovered the equivalent in PG -- sequences/serial field.  I would
like to use serial if possible, but here's the problem:  getting the
existing records over while *keeping the existing values* for the
Identity/sequence ID fields.  They actually transfer over fine, but
the next insert into the  PG table generates a duplicate ID error.
Obviously my transfer did not update the sequence used by the serial
field -- it's still stuck at 1.  However, I'm just not sure of the
best course of action.

I have the PG Developer's Handbook, but the only example that applies
assumes that I would rewrite the serial fields (ie, let them
auto-generate by not giving values during the import/transfer).  This
will not work due to the fact that those IDs are used as foreign keys
in other tables.


I've thought about the following as solutions:

1.  Do the transfer, put values into the serial field, then find the
highest value and manually set the sequence somehow to start from
highest+1 (seed value).  (I understand there is a problem with orphan
sequences with serial fields if you drop tables, so maybe I shouldn't
use serials anyway?)

2.  Stop using autonumber-type fields!  I have used random-character
unique IDs (varchar) in the past; if they are a more solid solution I
will use that approach.

3.  Let the code increment the ID (ie, find highest and increment),
and make the ID an int field -- not the best solution due to
possibility of simultaneous inserts, plus now more logic moves into
the application.

4.  Update the foreign keys in related tables with the new values as I
do the transfer -- this would have to be something writting in PG PSQL
or php etc due to the logic involved.

5.  ...your suggestions...

Thanks for any help you can provide.

Rick