First of all, consider using a different migration method altogether. A Foreign Data Wrapper would let you query MySQL tables as if they were part of the PostgreSQL database.
PostgreSQL tends to validate data more strictly than MySQL. It's quite possible that your MySQL database contains data that PostgreSQL would reject . In that case, you would end up with a half-finished migration: some rows will have been copied, some will not, and it would be a chore to fix the offending row and resume the process. Therefore, I recommend using a transaction for the entire PostgreSQL session, so that it either succeeds or does nothing. (Alternatively, include an ORDER BY clause on the MySQL queries so that there is some hope of being able to resume the migration deterministically.)
You are trying to do two things at once: transfer the data, and change the schema. For clarity, consider doing them as separate steps. One way to do it would be INSERT INTO TEMPORARY TABLE tmp_memberships ... to copy the data into PostgreSQL verbatim (which you can skip if using a Foreign Data Wrapper), followed by
INSERT INTO memberships (group_id, user_id, update_date, update_member_id, approval_date, status)
SELECT group_id, user_id, update_date, update_user_id, update_date, status
FROM tmp_memberships;
... to rename and add a column.
First of all, consider using a different migration method altogether. A Foreign Data Wrapper would let you query MySQL tables as if they were part of the PostgreSQL database.
PostgreSQL tends to validate data more strictly than MySQL. It's quite possible that your MySQL database contains data that PostgreSQL would reject . In that case, you would end up with a half-finished migration: some rows will have been copied, some will not, and it would be a chore to fix the offending row and resume the process. Therefore, I recommend using a transaction for the entire PostgreSQL session, so that it either succeeds or does nothing. (Alternatively, include an ORDER BY clause on the MySQL queries so that there is some hope of being able to resume the migration deterministically.)
You are trying to do two things at once: transfer the data, and change the schema. For clarity, consider doing them as separate steps. One way to do it would be INSERT INTO TEMPORARY TABLE tmp_memberships ... to copy the data into PostgreSQL verbatim (which you can skip if using a Foreign Data Wrapper), followed by
INSERT INTO memberships (group_id, user_id, update_date, update_member_id, approval_date, status)
SELECT group_id, user_id, update_date, update_user_id, update_date, status
FROM tmp_memberships;
... to rename and add a column.
First of all, consider using a different migration method altogether. A Foreign Data Wrapper would let you query MySQL tables as if they were part of the PostgreSQL database.
You are trying to do two things at once: transfer the data, and change the schema. For clarity, consider doing them as separate steps. One way to do it would be INSERT INTO TEMPORARY TABLE tmp_memberships ... to copy the data into PostgreSQL verbatim (which you can skip if using a Foreign Data Wrapper), followed by
INSERT INTO memberships (group_id, user_id, update_date, update_member_id, approval_date, status)
SELECT group_id, user_id, update_date, update_user_id, update_date, status
FROM tmp_memberships;
... to rename and add a column.
First of all, consider using a different migration method altogether. A Foreign Data Wrapper would let you query MySQL tables as if they were part of the PostgreSQL database.
PostgreSQL tends to validate data more strictly than MySQL. It's quite possible that your MySQL database contains data that PostgreSQL would reject. In that case, you would end up with a half-finished migration: some rows will have been copied, some will not, and it would be a chore to fix the offending row and resume the process. Therefore, I recommend using a transaction for the entire PostgreSQL session, so that it either succeeds or does nothing. (Alternatively, include an ORDER BY clause on the MySQL queries so that there is some hope of being able to resume the migration deterministically.)
You are trying to do two things at once: transfer the data, and change the schema. For clarity, consider doing them as separate steps. One way to do it would be INSERT INTO TEMPORARY TABLE tmp_memberships ... to copy the data into PostgreSQL verbatim (which you can skip if using a Foreign Data Wrapper), followed by
INSERT INTO memberships (group_id, user_id, update_date, update_member_id, approval_date, status)
SELECT group_id, user_id, update_date, update_user_id, update_date, status
FROM tmp_memberships;
... to rename and add a column.