Thread: Migration problem - serial fields
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
I think Database Migration Wizard in PgAdminII can solve your problems. You can find it at pgadmin.postgresql.org . Serkan -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Rick Sent: 04 Mart 2002 Pazartesi 19:25 To: pgsql-general@postgresql.org Subject: [GENERAL] Migration problem - serial fields 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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
rick@planetdigital.com (Rick) writes:
> 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.
No, it wouldn't. After you transfer the data, advance the sequence
with something like
SELECT setval('seqname', (SELECT max(serialcol) FROM table));
and then you're good to go.
regards, tom lane
> 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?) I'd suggest to use this sollution. I have had the same problem some time ago and solved it by inserting my tables and then creating the erial. Cheers Jochen