Ruby-on-rails Rails 迁移 - 带类型转换的 change_column
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17075173/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rails migrations - change_column with type conversion
提问by Mike Szyndel
I already google'd aroung a little bit and seems there's no satisfying answer for my problem.
我已经用谷歌搜索了一下，似乎我的问题没有令人满意的答案。
I have a table with column of type string. I'd like to run following migration:
我有一个包含字符串类型列的表。我想运行以下迁移：
class ChangeColumnToBoolean < ActiveRecord::Migration
def up
change_column :users, :smoking, :boolean
end
end
When I run this I get following error
当我运行它时，我收到以下错误
PG::Error: ERROR: column "smoking" cannot be cast automatically to type boolean
HINT: Specify a USING expression to perform the conversion.
: ALTER TABLE "users" ALTER COLUMN "smoking" TYPE boolean
I know I can perform this migration using pure SQL but still it would be nicer if I could do it with Rails. I went through Rails code and seems theres no such possibility, but maybe someone knows a way?
我知道我可以使用纯 SQL 执行此迁移，但如果我可以使用 Rails 执行此操作会更好。我浏览了 Rails 代码，似乎没有这种可能性，但也许有人知道方法？
I'm not interested in: - pure SQL - dropping the column - creating another column, converting data, dropping original and then renaming
我不感兴趣： - 纯 SQL - 删除列 - 创建另一列，转换数据，删除原始然后重命名
采纳答案by Mike Szyndel
Since I'm using Postgres, I went with SQL solution for now. Query used:
由于我使用的是 Postgres，所以我现在使用 SQL 解决方案。使用的查询：
execute 'ALTER TABLE "users" ALTER COLUMN "smoking" TYPE boolean USING CASE WHEN "flatshare"=\'true\' THEN \'t\'::boolean ELSE \'f\'::boolean END'
It works only if one has a field filled with true/false strings (such as default radio button collection helper with forced boolean type would generate)
仅当一个字段填充了真/假字符串时才有效（例如会生成具有强制布尔类型的默认单选按钮集合助手）
回答by Brian
If your strings in smokingcolumn are already valid boolean values, the following statement will change the column type without losing data:
如果smoking列中的字符串已经是有效的布尔值，则以下语句将更改列类型而不会丢失数据：
change_column :users, :smoking, 'boolean USING CAST(smoking AS boolean)'
Similarly, you can use this statement to cast columns to integer:
同样，您可以使用此语句将列转换为整数：
change_column :table_name, :column_name, 'integer USING CAST(column_name AS integer)'
I am using Postgres. Not sure whether this solution works for other databases.
我正在使用 Postgres。不确定此解决方案是否适用于其他数据库。
回答by Matt
Not all databases allow changing of column type, the generally taken approach is to add a new column of the desired type, bring any data across, remove the old column and rename the new one.
并非所有数据库都允许更改列类型，通常采用的方法是添加所需类型的新列，将所有数据引入，删除旧列并重命名新列。
add_column :users, :smoking_tmp, :boolean
User.reset_column_information # make the new column available to model methods
User.all.each do |user|
user.smoking_tmp = user.smoking == 1 ? true : false # If smoking was an int, for example
user.save
end
# OR as an update all call, set a default of false on the new column then update all to true if appropriate.
User.where(:smoking => 1).update_all(:smoking_tmp = true)
remove_column :users, :smoking
rename_column :users, :smoking_tmp, :smoking
回答by Денис Епишкин
So right for boolean in postgres:
所以对于 postgres 中的 boolean 来说是正确的：
change_column :table_name, :field,'boolean USING (CASE field WHEN \'your any string as true\' THEN \'t\'::boolean ELSE \'f\'::boolean END)'
and you may add some more WHEN- THENcondition in your expression
您可以在表达式中添加更多WHEN-THEN条件
For other database servers, the expression will be constructed based on the syntax for your database server, but the principle is the same. Only manual conversion algorithm, entirely without SQL there is not enough unfortunately.
对于其他数据库服务器，表达式将根据您的数据库服务器的语法构造，但原理是相同的。只有手动转换算法，完全没有SQL 有不够可惜。
Way with syntax change_column :table, :filed, 'boolean USING CAST(field AS boolean)'is suitable only if the contents of the field something like: true / false / null
change_column :table, :filed, 'boolean USING CAST(field AS boolean)'仅当字段的内容类似于：true / false / null 时，使用语法的方式才适用
