chensan

chensan

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

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.

Marked As Solved

fmcgeough

fmcgeough

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
16
Post #2

Also Liked

OvermindDL1

OvermindDL1

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

fmcgeough

fmcgeough

Oooo thanks! I wasn’t aware of that!

chensan

chensan

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

Where Next?

Popular in Questions Top

Harrisonl
We have an ECS cluster with 4 services, where each task joins a single cluster, via discovery ECS discovery service. Currently when I de...
New
siddhant3030
Hi, I have to write a raw query for one of my project. But till now I have used ecto queries and don’t have much experience writing raw ...
New
greenz1
I have a phoenix application from which a user can download multiple(5-6) files of size 1MB. I couldn’t find anything related to sending ...
New
New
freewebwithme
Using vs code and installed ElixirLS: support and debugger. And I got an error popped up on start up says Failed to run ‘elixir’ comma...
New
vegabook
I'm brand new to Phoenix and I have stripped one of the demo applications to the bone. I just want to get an svg up on the screen. Here i...
New
baxterw3b
Hi guys, i’m new in the Elixir world, and i have to say, that i love it! i’m having some problem to understand anonymous functions with ...
New
jason.o
In the code below, if the create action is not set to accept “extra_key” as an input, it errors out with a message shown above. Is there ...
New
srinivasu
How to handle excepions in elixir? Suppose i have A, B, C ,D, E modules. and each module has get() function. A.get() method will call th...
New
shijith.k
I am trying to start a new phoenix project with elixir 1.9, but mix phx.new does not work. It says that ** (Mix) The task "phx.new" could...
New

Other popular topics Top

marius95
Hello everyone, I try to use an Javascript Event Handler in my root.html.leex file. Therefore I created a function in the app.js file: ...
New
gshaw
What is the idiomatic way of matching for not nil in Elixir? E.g., First way: defp halt_if_not_signed_in(conn, signed_in_account) when...
New
shahryarjb
Hello, I have map which I want to convert it to string like this: the map: %{last_name: "tavakkoli", name: "shahryar"} the string I ne...
New
belgoros
I’m not a pro in using Regex and can’t figure out why the following behaviour happens, especially if we take into account the difference ...
New
gausby
I asked this very same question on twitter and got some interesting feedback, but I thought it would be a good question to ask here as we...
1207 39247 209
New
SoCreat
i’m a new one to elixir which editor can i use vs code? or atom? Thanks! :smiley:
New
AstonJ
We’ve put together this wiki for Phoenix LiveView - please feel free to add any info you feel is worth including. What is Phoenix LiveV...
New
jononomo
For some reason my phoenix channels are working for me in my local dev environment, but as soon as I deploy via Docker, I get a 403 error...
New
sergio
Kind of like when jquery came out, it was super necessary. Existing drag and drop libraries have a bunch of baggage to support old browse...
New
vonH
In asking this question I am more interested about the expressiveness of the language itself and less concerned about the availability of...
New

We're in Beta

About us Mission Statement