Yep this would remove all data on the column, which is not what the original poster wanted.
I’m fairly sure this should happen automatically if converting from a string column to an integer column. See here for conditions when this will fail.
samwightt
benwilson512
Author of Craft GraphQL APIs in Elixir with Absinthe
evadne
For the OP. In Postgres you can define an enum and in Postgrex/Ecto interact with the column as if it was a varchar / text field so you can keep on doing what you are doing in the Elixir app. Using integer fields to back an application-defined enum field smells like what Rails would do and is not really recommended because when you look at the DB you just see a bunch of numbers.
Please study PostgreSQL: Documentation: 18: 8.7. Enumerated Types
If you insist, you can use a case statement to avoid doing multiple updates
1
amnu3387
This is one of those questions that look fairly simple but in practical terms can be quite complex, building on what ben said,
step 1
- add the new column
step 2
- change either the db or app to be able to automatically save the new values as well as the old ones
- update all existing rows to have the new column filled
step 3
- roll out a deploy to a new version of your app that only uses the new column in its schema definition and can translate requests using the old schema into new ones
- if you have a frontend as well do the same there
step 4
- if you created a db trigger on 2, drop it here too
- delete the old column
This would solve the question even if you have many nodes deployed but it really depends on - is it ok if some request errors out while we’re doing this? Or it’s of the uttermost importance that not a single request is botched. Depending on the answer for that you can make it easier or more complex.
note on step 2
can be either at the app level (changing the code paths that save those records to now save also the new col) or at the db level with a trigger that auto populates the new column based on the value of the old one whenever an insert or update is done for that table. This is needed because if someone would save a record between you updating the existing rows and you having finished the deployment of the new app version, it would still only save the old column for those requests.
note on step 3
gets more complex if you’re using a spa client, as they won’t receive the new “bundle” until they refresh their page. So even if you do all these steps they will be stuck on a version of code that expects the old schema and functions with the old schema alone (forms, etc). The best UX is to somehow display a warning to the user telling them they should refresh (like adding a warning/info when trying to save a form and you detect that the form only has the old column and not the new one). With html only (no spa clients) you can just redirect or serve the new version on submission.
It kinda boils down to what you need to guarantee while doing this. Sometimes it’s ok to go YOLO.
1