Migrate a column to a new table in a one-to-many relationship

[cmetzlerhg]

I have the following scenario: In a table users, I have an address but due to business requirements, a user should be able to enter multiple addresses. So I would like to do a migration that:

• Creates a new table (address) and initialize the columns
• Each address has a foreign key constraint referencing the user's id
• The current address in the user table should be moved to the new table

Is this possible with pgroll without using plain SQL migrations?

[andrew-farries]

I don't think there is a way to do this without using a raw SQL migration.

In future it would be good to be able to specify higher-level migrations like this without having to use raw SQL but that isn't possible today.

[cmetzlerhg]

Thanks @andrew-farries. So maybe it would be sufficient for now, to extend the docs for raw SQL migrations. It's not clear to me, how this is supposed to work. My thought is, that I need 2 migrations to achieve this, because the docs says a SQL migration cannot run along with other migrations:

1. Create the new table with constraints etc. (could be done with a regular pgroll migration)
2. Define up and down migrations via raw SQL migration (e.g. copy data from the old table to the new table etc)

I would expect that pgroll then will take care of creating the new schema, copying data and create triggers, is this correct?

[andrew-farries]

Yes, I think that's correct. One migration to create the new table and another for the data migration 👍