Are you trying to convert this into an actual database enum? If so, this should be relevant: https://stackoverflow.com/questions/15655820/upgrading-a-varchar-column-to-enum-type-in-postgresql

I believe that would remove all the data under :column_name

You can use modify/3

alter table("posts") do
  modify :title, :text
end

But you still need to handle typecasting the actual data in that column

See this thread Ecto Changeset: modify column type from :string to {:array, :string}

So what is the point in converting it into an integer then? OH! You’re trying to use ecto_enum library. Just use the built in Ecto.Enum — Ecto v3.14.0 type. It works fine with strings.

EDIT: If you’re trying to get the sortability property, I would seriously just consider using a proper database enum type and not trying to convert into integers.

IF however you really want to do this, here is how:

1. Make a new column of type integer called new_column_name.
2. Run a series of updates:

update table_name set new_column_name = 0 where column_name = "verifiy"
update table_name set new_column_name = 1 where column_name = "invite"
... etc

3. Drop the old column and rename the new column to column.

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

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.