Ruby-on-rails Rails 迁移：尝试将列的类型从字符串更改为整数

Rails Migrations: tried to change the type of column from string to integer

提问by banditKing

I created a table in my rails app with rails generate migrations command. Here is that migration file:

我使用 rails generate migrations 命令在我的 rails 应用程序中创建了一个表。这是迁移文件：

class CreateListings < ActiveRecord::Migration
  def change
    create_table :listings do |t|
      t.string :name
      t.string :telephone
      t.string :latitude
      t.string :longitude

      t.timestamps
    end
  end
end

Then I wanted to store the latitude and longitude as integers so I tried to run:

然后我想将纬度和经度存储为整数，所以我尝试运行：

rails generate migration changeColumnType

and the contents of that file are:

该文件的内容是：

class ChangeColumnType < ActiveRecord::Migration
  def up
    #change latitude columntype from string to integertype
    change_column :listings, :latitude, :integer
    change_column :listings, :longitude, :integer
    #change longitude columntype from string to integer type
  end

  def down  
  end
end

I was expecting the column type to change however the rake was aborted and the following error message appeared. I was wondering why this did not go through? Im using postgresql in my app.

我期待列类型改变，但是耙子被中止并出现以下错误消息。我想知道为什么这没有通过？我在我的应用程序中使用 postgresql。

rake db:migrate
==  ChangeColumnType: migrating ===============================================
-- change_column(:listings, :latitude, :integer)
rake aborted!
An error has occurred, this and all later migrations canceled:

PG::Error: ERROR:  column "latitude" cannot be cast to type integer
: ALTER TABLE "listings" ALTER COLUMN "latitude" TYPE integer

Tasks: TOP => db:migrate
(See full trace by running task with --trace)

NOTE: The table has no DATA. Thanks

注意：该表没有数据。谢谢

采纳答案by Erwin Brandstetter

I quote the manual about ALTER TABLE:

我引用了关于以下内容ALTER TABLE的手册：

A USING clause must be provided if there is no implicit or assignment cast from old to new type.

如果没有从旧类型到新类型的隐式或赋值转换，则必须提供 USING 子句。

What you need is:

你需要的是：

ALTER TABLE listings ALTER longitude TYPE integer USING longitude::int;
ALTER TABLE listings ALTER latitude  TYPE integer USING latitude::int;

Or shorter and faster (for big tables) in one command:

或者在一个命令中更短更快（对于大表）：

ALTER TABLE listings ALTER longitude TYPE integer USING longitude::int
                    ,ALTER latitude  TYPE integer USING latitude::int;

This works with or without dataas long as all entries are convertible to integer.
If you have defined a DEFAULTfor the column, you may have to drop and recreate that for the new type.

这个作品有或没有数据，只要所有项目均转换为integer。
如果您已DEFAULT为列定义了 a ，则可能必须删除并为新类型重新创建它。

Here is blog article on how to do this with ActiveRecord.
Or go with @mu's advice in the comment. He knows his Ruby. I am only good with the PostgreSQL here.

这是关于如何使用 ActiveRecord 执行此操作的博客文章。
或者在评论中使用@mu 的建议。他认识他的红宝石。我只擅长这里的 PostgreSQL。

回答by neverbendeasy

I would include the raw SQL in your migration file like below so that it updates schema.rb.

我会将原始 SQL 包含在您的迁移文件中，如下所示，以便它更新 schema.rb。

class ChangeColumnType < ActiveRecord::Migration
  def up
    execute 'ALTER TABLE listings ALTER COLUMN latitude TYPE integer USING (latitude::integer)'
    execute 'ALTER TABLE listings ALTER COLUMN longitude TYPE integer USING (longitude::integer)'
  end

  def down
    execute 'ALTER TABLE listings ALTER COLUMN latitude TYPE text USING (latitude::text)'
    execute 'ALTER TABLE listings ALTER COLUMN longitude TYPE text USING (longitude::text)'
  end
end

回答by rizidoro

I know this a bit ugly, but I prefer to just remove the column and add again with the new type:

我知道这有点难看，但我更喜欢删除列并使用新类型再次添加：

 def change
     remove_column :mytable, :mycolumn
     add_column :mytable, :mycolumn, :integer, default: 0
 end

回答by Joseph N.

The following is a more rails wayto approach the problem. For my case I had two columns in my purchases table that I needed to convert from type string to float.

下面是一个更rails way接近问题的方法。就我而言，我的采购表中有两列需要将字符串类型转换为浮点数。

def change
    change_column :purchases, :mc_gross, 'float USING CAST(mc_gross AS float)'
    change_column :purchases, :mc_fee, 'float USING CAST(mc_fee AS float)'
end

That did the trick for me.

这对我有用。

回答by Victor

1. Do you have existing data in those columns?
2. You should not use int for latitude and longitude. They should be in floating points instead.

1. 这些列中是否有现有数据？
2. 您不应将 int 用于纬度和经度。它们应该是浮点数。

回答by gilcierweb

latitude and longitude is decimal

纬度和经度是十进制的

rails g scaffold client name:string email:string 'latitude:decimal{12,3}' 'longitude:decimal{12,3}' 

class CreateClients < ActiveRecord::Migration[5.0]
  def change
    create_table :clients do |t|
      t.string :name
      t.string :email
      t.decimal :latitude, precision: 12, scale: 3
      t.decimal :longitude, precision: 12, scale: 3

      t.timestamps
    end
  end
end