Migration problem - serial fields
От | Rick Anderson |
---|---|
Тема | Migration problem - serial fields |
Дата | |
Msg-id | a60ggj$2941$1@jupiter.hub.org обсуждение исходный текст |
Ответы |
Re: Migration problem - serial fields
(Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Re: Migration problem - serial fields (Brent Verner <brent@rcfile.org>) |
Список | 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
В списке pgsql-general по дате отправления:
Предыдущее
От: "Ernesto E. Gutierrez"Дата:
Сообщение: Re: install psql 7.1.3 - rh6.2 - libreadline.so.4