Convert string column to integer in DB

Make a new migration file (in priv/repo/migrations) like:

defmodule YourApp.Repo.Migrations.StrToInt do
  use Ecto.Migration

  def change do
    alter table(:table_name) do
      remove :column_name
      add :column_name, :integer
    end
  end
end

Then run mix ecto.migrate

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}

That’s right. I assumed the app was not in production yet. If the data needs to be preserved, then disregard my reply.

This is a destructive migration. This will actually create more problems than solution

This helps.But it doest solve my issue completely. I my case, each different value should be converted to a different integer.
For example:
verify → 1
invite → 2

This is how I need to convert the field.

As you can already assume, I am not really an expert in SQL. So I don’t know how to update the query in the thread you shared in such way that it solves my problem

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

Not really. Using an database enum might be problematic as its suggested in the comments of the stack overflow question. I am trying to make use of EctoEnum to leverage similar use cases of a db enum.

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.

Thanks this helps

Still, out of curiosity, if I want to convert it in to integer - how can I do it?