Convert string column to integer in DB

Phoenix Forum Questions / Help
, ,
1

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?

2

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

3

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}

2 Likes
4

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

5

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

6

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

7

Are you trying to convert this into an actual database enum? If so, this should be relevant: sql - Upgrading a varchar column to enum type in postgresql - Stack Overflow

3 Likes
8

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.

9

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.5.8 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
  1. Drop the old column and rename the new column to column.
2 Likes
10

Thanks :raised_hands: this helps

11

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

12

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.

13

I updated my post.

14

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: 13: 8.7. Enumerated Types

If you insist, you can use a case statement to avoid doing multiple updates

1 Like
15

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 Like