Rails 迁移:尝试将列的类型从字符串更改为整数 [英] Rails Migrations: tried to change the type of column from string to integer

问题描述

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

class CreateListings <ActiveRecord::迁移定义改变create_table :listings 做 |t|t.string :namet.string : 电话t.string : 纬度t.string:经度t.时间戳结尾结尾结尾

然后我想将纬度和经度存储为整数，所以我试着跑:

rails 生成迁移 changeColumnType

那个文件的内容是:

class ChangeColumnType ActiveRecord::迁移清空#将纬度列类型从字符串更改为整数类型change_column :listings, :latitude, :integerchange_column :listings, :longitude, :integer#将经度列类型从字符串更改为整数类型结尾向下定义结尾结尾

我原以为列类型会发生变化，但是耙子已中止并出现以下错误消息.我想知道为什么这没有通过?我在我的应用中使用了 postgresql.

rake db:migrate== 更改列类型:迁移 ================================================-- change_column(:listings, :latitude, :integer)耙子中止！发生了错误，这次迁移和所有后来的迁移都取消了:PG::Error: ERROR: 列纬度"不能转换为整数类型: ALTER TABLE "listings" ALTER COLUMN "latitude" TYPE 整数任务:TOP =>数据库:迁移(通过使用 --trace 运行任务查看完整跟踪)

注意:该表没有数据.谢谢

解决方案

我引用手册 关于ALTER TABLE:

<块引用>

如果没有隐式或赋值，则必须提供一个 USING 子句从旧类型转换为新类型.

你需要的是:

<前>ALTER TABLE 列表 ALTER longitude TYPE integer USING longitude::int;ALTER TABLE 列表 ALTER latitude TYPE integer USING latitude::int;

或者在一个命令中更短更快(对于大表):

ALTER TABLE 列表 ALTER longitude TYPE integer USING longitude::int,ALTER latitude TYPE integer USING latitude::int;

只要所有条目都可以转换为整数，此无论有无数据都适用.
如果您为该列定义了 DEFAULT，您可能需要删除并为新类型重新创建它.

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

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

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.

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

解决方案

I quote the manual about ALTER TABLE:

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

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 data as long as all entries are convertible to integer.
If you have defined a DEFAULT for the column, you may have to drop and recreate that for the new type.

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.

这篇关于Rails 迁移:尝试将列的类型从字符串更改为整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！