Elixir Ecto, failed change type to jsonb

Questions / Help
,
1

I have an error while modifying column type to jsonb or map. Here is my migration file:

def change do
    alter table(:disbursements) do
      modify :reason, :map
    end
  end

and I receive error like below:

** (Postgrex.Error) ERROR 42804 (datatype_mismatch) column "reason" cannot be cast automatically to type jsonb

hint: You might need to specify "USING reason::jsonb".
(ecto_sql) lib/ecto/adapters/sql.ex:624: Ecto.Adapters.SQL.raise_sql_call_error/1

My question is how can I add extra command USING reason::jsonb to my migration file ?

2 
  def change do
    alter table(:disbursements) do
      modify :reason, :jsonb
    end
  end
3 Likes
3

Long time passed since this, but I’ve just ran into the same problem and wanted to share some feedback for future reference. I’ve found a solution based in this similar post.
While @benwilson512 solution works, it won’t if the database contains existing data in the :reason field. It must be cased from the previous type to the new one.

Asuming the old type for :reason is :text, the migration should execute the following:

...

def change do
  execute(&execute_up/0, &execute_down/0)
end

def execute_up() do
  repo().query!(
    "ALTER table disbursements ALTER column reason TYPE jsonb USING (disbursements::jsonb);",
    [],
    # Just an option to log the execution
    log: :info
  )
end

def execute_down() do
  repo().query!(
    "ALTER table disbursements ALTER column reason TYPE text;",
    [],
    # Just an option to log the execution
    log: :info
  )
end

...
2 Likes