Rails更改列类型并更新列值 [英] Rails change column type and update column values

问题描述

我有一个名为Users的表，其活动列为boolean。我很快将需要将该列的类型更改为字符串。我还需要将Active的所有列值从：true更改为 active，将：false更改为 inactive。

I have a table called Users with a column Active that is type boolean. I will soon need to change the type of that column to string. I will also need to change all of the column values of Active from :true to "active" and :false to "inactive".

要更改列类型，我会使用
将列类型从日期更改为ROR迁移期间的DateTime

To change the column type I would use Change a column type from Date to DateTime during ROR migration

class ChangeColumnTypeInUsers < ActiveRecord::Migration
  def up
    change_column :users, :active, :string
  end

  def down
    change_column :users, :active, :boolean
  end
end

如何更新列值，以便类型更改不会破坏它？会自动将：true转换为 true吗？

How would I update the column value so that the type change doesn't break it? Will it automatically convert :true to "true"?

推荐答案

使用 ALTER TABLE的USING子句：

可选的 USING 子句指定如何从旧的值计算新的列值；如果省略，则默认转换与从旧数据类型到新数据类型的转换相同。

The optional USING clause specifies how to compute the new column value from the old; if omitted, the default conversion is the same as an assignment cast from old data type to new.

简单的SQL类型转换将离开您使用字符串'true'和'false'这样的字符串，因此您确实想添加USING。我会绕过AR并手动完成：

A simple SQL type cast would leave you with the strings 'true' and 'false' so you do want to add a USING. I'd bypass AR and do it by hand:

connection.execute(%q(
  alter table users
  alter column active
  type text
  using case when active then 'active' else 'inactive' end
))

对您来说重要的部分是用例... 部分。您可以通过诱使AR做正确的事情来将其与通常的AR-ish change_column 一起使用：

The important part for you is the using case ... part at the end. You can use that together with the usual AR-ish change_column stuff by tricking AR into doing The Right Thing:

class ChangeColumnTypeInUsers < ActiveRecord::Migration
  def up
    change_column :users, :active, "text using case when active then 'active' else 'inactive' end"
  end

  def down
    change_column :users, :active, "boolean using active = 'active'"
  end
end

请注意，我正在使用 text 作为列类型。当您说：string 无限制时，Rails将在数据库内部使用 varchar（255），这对于PostgreSQL来说是毫无意义的因为它处理所有字符串类型的存储内部几乎相同，所以对的长度有限制char（n）和 varchar（n）实际上使它们的使用比 text 。只有当您有理由包括一个特定的：limit 时，PostgreSQL才有意义：string （然后长度受 CHECK 约束的 text 列会更有意义，但AR太笨了，无法了解高级 CHECK 约束之类的东西）。

Note that I'm using text as the column type. Rails will using varchar(255) inside the database when you say :string without a limit, that's pretty pointless with PostgreSQL as it handles the storage for all the string types pretty much the same internally, the length restrictions on char(n) and varchar(n) actually make them more expensive to use than text. Then only time :string makes sense with PostgreSQL is when you have a reason to include a specific :limit (and then a text column with a CHECK constraint on the length would make more sense but AR is too dumb to know about "advanced" things like CHECK constraints).

这篇关于Rails更改列类型并更新列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！