Rake DB:Migration error when modifying the column type

Question

I have written my migration file to change the column type as follows

class ChangeColumnTypeInMyTable < ActiveRecord::Migration
   def self.up




     execute <<-SQL
       ALTER TABLE batches
         ALTER COLUMN updated_by int
      SQL

     execute <<-SQL
        ALTER TABLE batches
          ALTER COLUMN created_by int
     SQL

 end

def self.down
  end
end

but this gives me an error saying PG::SyntaxError: ERROR: syntax error at or near "int" LINE 2: ALTER COLUMN updated_by int I could not find the error . Thank you in advance

score 1 · Accepted Answer · edited May 23 '17 at 11:44

1

ALTER COLUMN updated_by TYPE int USING (updated_by::integer)

Change type of varchar field to integer: "cannot be cast automatically to type integer" could help.

edited May 23 '17 at 11:44

Community

1
1

answered Sep 10 '13 at 11:26

score 0 · Answer 2 · answered Sep 10 '13 at 11:32

0

I guess the word TYPE is needed here...

Reference: http://www.postgresql.org/docs/8.0/static/sql-altertable.html

class ChangeColumnTypeInMyTable < ActiveRecord::Migration

  def self.up
    execute <<-SQL
     ALTER TABLE mt940_batches
     ALTER COLUMN updated_by TYPE int
    SQL

    execute <<-SQL
     ALTER TABLE mt940_batches
     ALTER COLUMN created_by TYPE int
    SQL
  end

  def self.down
  end
end

answered Sep 10 '13 at 11:32

Vamsi Krishna

3,742
4
20
45

um getting this error PG::DatatypeMismatch: ERROR: column "updated_by" cannot be cast to type integer : ALTER TABLE mt940_batches ALTER COLUMN updated_by TYPE int – not 0x12 Sep 10 '13 at 14:07
Did you try the solution provided by @GraemeMcLean? That actually does an explicit cast. – Vamsi Krishna Sep 10 '13 at 14:17
@Kalanamith, did you see the link in my answer? There's talk on there specifically about your error - and some people have used trim in the cast. Maybe that can help you. – Sep 10 '13 at 21:54

score 0 · Answer 3 · answered Sep 10 '13 at 11:38

0

As you are using SQL so accordingly you need to use ActiveRecord in this case :

def self.up
connection = ActiveRecord::Base.connection()
connection.execute(put_your_sql_query_here)
end

Hope rest you can figure out easily.

answered Sep 10 '13 at 11:38

Ajay

4,199
4
27
47

Rake DB:Migration error when modifying the column type

3 Answers3