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

Hi,

I’d like to change a database column’s type from :string to {:array, :string}. Migration should work as follows: "my_value" -> ["my_value"].

I’ve looked in the Ecto documentation, and I tried doing the migration via

modify :column_name, {:array, :string}, from: :string

But that doesn’t work. I’ve also seen from the Postgres documentation that I can use the USING option to indicate how to do the conversion, and the error message when I try to run the migration indicates as much:

hint: You might need to specify "USING column_name::character varying(255)[]"

I don’t see how to specify this option in the modify function, though.

What’s the most straightforward way to handle this conversion? Am I missing something obvious? Do I need to use raw Postgres, or something more complicated? Thanks!

2 Likes

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.
1 Like

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.

4 Likes

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.

3 Likes

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.

3 Likes

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.

2 Likes

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
5 Likes

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

2 Likes

Sorry to necro an old thread, but just wanted to pop in here and add the above was very helpful, however I got a malformed array literal error from PSQL when trying to use the method above for specifying an empty array [].

Resolution that worked for me, in case anyone else is struggling with the same issue and winds up here, was to use array[]::varchar[] instead of []

Didn’t work:
execute("ALTER TABLE table_name ALTER COLUMN column_name type varchar(255)[] using string_to_array(column_name, ','), ALTER column_name SET default '[]' ")

Worked:
execute("ALTER TABLE table_name ALTER COLUMN column_name type varchar(255)[] using string_to_array(column_name, ','), ALTER column_name SET default array[]::varchar[]")

My SQL/PSQL skills are very poor however, so maybe I’m just missing something.

1 Like

What you have is fine but you should be able to do the array literal as well. The problem is that '[]' is not the syntax for array literals, '{}' is. A quick demonstration…

127.0.0.1(from musebms).msmcp_dev.scb.5432 [Thu Nov 17 02:56:13 PM PST 2022]
> create table scb_test (id bigint primary key generated always as identity, test_col text);
CREATE TABLE
Time: 15.896 ms
127.0.0.1(from musebms).msmcp_dev.scb.5432 [Fri Nov 18 09:52:24 AM PST 2022]
> alter table scb_test alter column test_col type text[] using string_to_array(test_col, ',');
ALTER TABLE
Time: 51.545 ms
127.0.0.1(from musebms).msmcp_dev.scb.5432 [Fri Nov 18 09:54:26 AM PST 2022]
> alter table scb_test alter column test_col set default '{}';
ALTER TABLE
Time: 2.799 ms

So the following should work (though I have not tested)…

execute("""
  ALTER TABLE table_name ALTER COLUMN column_name type varchar(255)[] 
    USING string_to_array(column_name, ','), ALTER column_name SET DEFAULT '{}' 
  """)

Admittedly I do prefer to specify the type with the literal syntax like: '{}'::varchar(255)[]

2 Likes

I figured it was something basic I didn’t understand – thanks!