dcrck
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!
Marked As Solved
dcrck
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
Also Liked
LostKobrakai
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.
LostKobrakai
There’s execute for executing arbitrary sql in migrations.
dimitarvp
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.
dimitarvp
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.
samcdavid
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
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Enabling companies to succeed by building software people love.
Categories:
Sub Categories:
Forums
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Develop your skills with books, videos, and courses.
Enabling companies to succeed by building software people love.
The deployment platform built for Elixir: PaaS ease, VPS control.
Courses that'll move you from confusion to "Aha, now I get it!"