(Postgrex.Error) ERROR 42804 (datatype_mismatch): column "" cannot be cast automatically to type integer

Questions / Help
1

I have a User schema with a :from_id field set to type :string:

defmodule TweetBot.Repo.Migrations.CreateUsers do
  use Ecto.Migration

  def change do
    create table(:users) do
      add :from_id, :string
      add :access_token, :string

      timestamps()
    end

    create unique_index(:users, [:from_id])
  end
end

Later I want to change type of :from_id to :integer,

defmodule TweetBot.Repo.Migrations.AlterUsers do
  use Ecto.Migration

  def change do
    alter table(:users) do
      modify(:from_id, :integer)
    end
  end
end

But I got this error running mix ecto.migrate:

** (Postgrex.Error) ERROR 42804 (datatype_mismatch): column “from_id” cannot be cast automatically to type integer

I just thought it might because I have data in database, so I drop the whole database, and run mix ecto.migrate again, the error still shows.

I also search the ecto repo on github, no simillar issue there. Seems there’s an answer on stackoverflow https://stackoverflow.com/questions/13170570/change-type-of-varchar-field-to-integer-cannot-be-cast-automatically-to-type-i for Rails, but how can I do it in ecto?

Thanks if anyone can help.

4 Likes
2

Yeah. That’s a Postgresql restriction. You’ll have to execute SQL to convert it.

alter table users alter column from_id type integer using (from_id::integer);

if you use psql and attempt to do the SQL directly it actually provides a hint on this:

HINT: You might need to specify “USING from_id::integer”.

so:

defmodule TweetBot.Repo.Migrations.AlterUsers do
  use Ecto.Migration

   def up do
      execute """
        alter table users alter column from_id type integer using (from_id::integer)
       """
   end

   def down do
      execute """
        alter table users alter column from_id type character varying(255);
       """
   end
end
15 Likes
3

@fmcgeough execute/2 exists that takes both an up and down argument so you don’t need to split them. :slight_smile:

6 Likes
4

Oooo thanks! I wasn’t aware of that!

1 Like
5

@OvermindDL1 @fmcgeough Thank you both for your help :slight_smile:

1 Like