So how would you do it safely? You break it in multiple steps:
- add a new integer column
- use a script to backfill the data to the new column, converting it
- switch the application to use the new column
- drop the original text column
This is an example of the expand/contract pattern.
- add a new integer column
- use a script to backfill the data to the new column, converting it
- switch the application to use the new column
- drop the original text column
This is an example of the expand/contract pattern.
Comments
pgroll automates this for you. For the "change type" migration type, it does pretty much exactly the above steps.
https://github.com/xataio/pgroll
It uses Postgres view to show both the old version of the schema and the new version of the schema **at the same time**.
This means you can do a rolling upgrade of your app without worries.