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!  If this would be better
posted in the novice group, please let me know. As a bit of background,
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 in the
"right way".  A side-effect of doing small web/db projects.

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 had
run 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.  It seems obvious that my transfer did not
update the sequence used by the serial field.  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 don't know if this means moving away from serial field to a
"nextval of sequence" approach.  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.  ...your suggestions...

Thanks for any help you can provide.

Rick