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!
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.
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.
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.
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
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.
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)[]
