I have a varchar field in my table. It has values like verify and invite. I want to use enum instead of this, and I am planning to use Ecto Enum . Now to do that I want to convert the varchar field to integer. How can I write a query to change the existing string values to integer values?

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

[image] APB9785: alter table(:table_name) do remove :column_name add :column_name, :integer end 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 t…

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 :sweat_smile:

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 …

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 en…

Thanks :raised_hands: this helps

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

Elixir Programming Language Forum

Convert string column to integer in DB

Questions & Help Questions

benwilson512 March 27, 2021, 5:27pm 13

I updated my post.