0

I have a web-based database application that currently uses a mysql database. We're moving from mysql to pgsql so we're moving data from the mysql db to the new pgsql db. In the data there are header records in one table, detail records in another table. The header records have a serial field for a record id and the detail records have a numeric field that holds the header record id to tie the detail record to the header record. Since the application that uses this data relies on the database to generate record ids when records are created, that same structure exists in the target pgsql database. How can data be copied from the mysql database to the pgsql database and maintain the header/detail id relationship? It would seem the header records will get whatever serialized value is next for their id, and the detail records will get added still holding the old mysql header record id.

michaelj77
  • 1
  • 1
  • The answer depends on how exactly you have defined your tables in Postgres; unfortunately, you chose not to provide this crucial information. May be one of these answers will help you: [1](https://stackoverflow.com/questions/55300370/postgresql-serial-vs-identity) and [2](https://stackoverflow.com/questions/35120544/override-serial-sequence-in-postgresql-with-entity-framework-c) – mustaccio May 27 '20 at 17:46

2 Answers2

0

Postgres uses sequences for tracking auto increment primary keys. You can insert your primary and foreign keys as-is from MySQL. You will then need to update the sequences to have the max(id) based on your data.

see: https://www.postgresql.org/docs/current/functions-sequence.html

for example:

SELECT setval('your_table_name_seq', select max(id) from your_table_name);

Note the "_seq" suffix for the setval param.

Timmus Agersea
  • 34
  • 4
-1
class Employee(NamedTuple):  # inherit from typing.NamedTuple
    name: str
    id: int = 3  # default value

employee = Employee('Guido')
assert employee.id == 3
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Gradi Yanto
  • 1
  • 1
  • 2
    Why should the OP ___try this___? **Good answers** will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO that may find this question and be reading your answer. If it is well answered maybe they will UpVote it. – RiggsFolly May 27 '20 at 15:57