There is actually an example in the README on how to change a column from TEXT to INTEGER, the technique would be the same the other way around: https://github.com/fabianlindfors/reshape#alter-column
For the cases that require manual handling, that is a bit tricky. I'm not sure Reshape would be able to automate that in any meaningful way so the best thing might just be to fall back to a standard procedure of making the changes in two separate migrations, where the manual changes are done in between.
> Another one is adding foreign keys where the existing data does not conform to the foreign key constraint.
I have given this some thought before as I wanted to add a migration which can add new foreign keys. I think it can be done by writing some migrations first which update the existing data to conform to the constraint, for example adding missing values. This can be done with `alter_column` right now and there will be more comprehensive migrations for data changes in the future.
I'm not very familiar with PostgreSQL, how do you handle the casts during inserts/updates?
> For the cases that require manual handling, that is a bit tricky.
Yeah in our case we just blocked the schema change and complained. Our support team would then take a look, possibly escalating to development. Not often such a change is done though.
> for example adding missing values
In our case we just delete the offending values if the constraint has "on cascade delete", which most do. We do take a backup of the database before doing schema changes though.
In the example, you can see "up" and "down" settings on the migration. These are SQL expressions which specify how to convert between the old and new schema, in this case casting from TEXT to INTEGER and back. So in this case we have:
up = "CAST(reference AS TEXT)" down = "CAST(reference AS INTEGER)"
which is just a standard Postgres function for casting.
> In our case we just delete the offending values if the constraint has "on cascade delete", which most do.
Interesting! I might add some options to a future `add_foreign_key` migration which can achieve behavior like this automatically.
Right, I get how that would work with a read-only view, I just didn't know you could do updates against such a view. Nifty!
We have migrated a few fields from varchar to integer. How would your solution deal with this? And of course in some cases there will be data that requires manual handling.
Another one is adding foreign keys where the existing data does not conform to the foreign key constraint.