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

I’m assuming the migration isn’t working because it’s not able to convert the current values in the column to the new type.

You might want to do this in multiple migrations:

• A migration to create a new column with type {:array, :string} and copy all the old column values to the new column as a list of strings.
• A migration to delete the old column.
• A migration to rename the new column to be the name of the deleted column.

Thanks for the suggestions. After sleeping on it, I ended up deciding to pluralize the column name. So this simply became a matter of moving the values from one column to the other.

Here’s what I ended up doing:

defmodule MyApp.Repo.Migrations.ConvertColumnToList do
  use Ecto.Migration

  alias MyApp.{Repo, MyTable}
  import Ecto.Query

  def change do
    alter table :my_table do
      add :list_column_name, {:array, :string}, default: []
    end

    flush()

    Repo.all(MyTable)
    |> Enum.each(fn row ->
      row
      |> MyTable.changeset(%{ name: row.name, list_column_name: [ row.column_name ]})
      |> Repo.update()
    end)

    alter table :my_table do
      remove :column_name
    end

  end
end

You should try to not use Repo in migrations. You don’t want to fall in a situation, where you need a migration to be applied to be able to successfully start the repo, while the migration needs the repo to be applied.

The other reason is that the repo is started by your application, so your whole application will be started up – which might have other sideeffects – just for migrations to be applied. While without the repo it’s enough for your app to be loaded (for the credentials), but only ecto needs to be started.

Okay, is there a way to solve this problem without using Repo?

I see Repo used in Programming Ecto when changing both the table structure and the data, as is the case here.

There’s execute for executing arbitrary sql in migrations.

Yep, better use raw SQL, otherwise you might find yourself in a situation when the runtime doesn’t allow you to use Elixir code to do or rollback a migration.

Thanks @dimitarvp and @LostKobrakai, here’s what I ended up settling on:

defmodule Database.Repo.Migrations.StringToListMigration do
  use Ecto.Migration

  def change do
    execute(&string_to_list/0, &list_to_string/0)
  end

  defp string_to_list() do
    repo().query!("alter table my_table alter column_name type varchar(255)[] using array[column_name], alter column_name set default '{}';", [], [log: :info])
  end

  defp list_to_string() do
    repo().query!("alter table my_table alter column_name type varchar(255) using column_name[1], alter column_name drop default;", [], [log: :info])
  end
end

I do the renaming / other cleanup operations in a separate migration.

Mostly good, but remove the repo().query!(...) stuff from the front. The logging level has a default so you shouldn’t worry about it. Just use execute with both strings as they are and nothing else.

I see.

Actual final solution:

defmodule Database.Repo.Migrations.StringToListMigration do
  use Ecto.Migration

  def change do
    execute(string_to_list(), list_to_string())
  end

  defp string_to_list() do
    "alter table my_table alter column_name type varchar(255)[] using array[column_name], alter column_name set default '{}'"
  end

  defp list_to_string() do
    "alter table my_table alter column_name type varchar(255) using column_name[1], alter column_name drop default"
  end
end

For my situation, using alter table profiles alter ethnicity type varchar(255)[] using string_to_array(ethnicity, ','), alter ethnicity set default '{}' on a PostgreSQL database separated my string into a list for me.

Likewise, if you need to rollback you can do alter table profiles alter ethnicity type varchar(255) using array_to_string(ethnicity, ','), alter column_name drop default