postgresql Rails 迁移更改列以使用 Postgres 数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22550066/
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 Migration changing column to use Postgres arrays
提问by rii
I am trying to change a column in my database so that it can use the Postgres array data type. Currently the table column is of type string.
我正在尝试更改数据库中的一列，以便它可以使用 Postgres 数组数据类型。当前表列的类型是字符串。
I am using the following migration to convert it:
我正在使用以下迁移来转换它：
def change
change_column :table, :dummy_column, :text, array: true, default: []
end
But I get the following error:
但我收到以下错误：
bundle exec rake db:migrate
rake aborted!
An error has occurred, this and all later migrations canceled:
PG::Error: ERROR: column "dummy_column" cannot be cast automatically to type character varying[]
HINT: Specify a USING expression to perform the conversion.
: ALTER TABLE "table" ALTER COLUMN "dummy_column" TYPE character varying(255)
Tasks: TOP => db:migrate
回答by Craig Ringer
PostgreSQL doesn't know how to automatically convert a column of varcharinto an array of varchar. It doesn't know what you might intend, because it has no way to know what format you think the current values are in.
PostgreSQL 不知道如何自动将 的列转换varchar为varchar. 它不知道您可能打算做什么，因为它无法知道您认为当前值采用什么格式。
So you need to tell it; that's what the USINGclause is for.
所以你需要告诉它；这就是该USING条款的用途。
ActiveRecord doesn't seem to explicitly support the USINGclause (not surprising, as it barely supports even the most basic database features). You can specify your own SQL text for the migration, though.
ActiveRecord 似乎没有明确支持该USING子句（并不奇怪，因为它甚至几乎不支持最基本的数据库功能）。不过，您可以为迁移指定自己的 SQL 文本。
Assuming your strings are comma separated and may not themselves contain commas, for example:
假设您的字符串以逗号分隔，并且本身可能不包含逗号，例如：
def change
change_column :table, :dummy_column, "varchar[] USING (string_to_array(dummy_column, ','))"
end
(I don't use Rails myself and haven't tested this, but it's consistent with the syntax used in examples elsewhere).
（我自己不使用 Rails，也没有对此进行测试，但它与其他地方示例中使用的语法一致）。
回答by lrrthomas
Using Rails 4.2 on postgresql 9.4 I was looking to do this and preserve my pre-existing string data as the first element in one element arrays.
在 postgresql 9.4 上使用 Rails 4.2 我希望这样做并将我预先存在的字符串数据保留为一个元素数组中的第一个元素。
It turns out that postgresql cannot coerce a string into a text array without a USING expression to tell it how.
事实证明，如果没有 USING 表达式告诉它如何，postgresql 无法将字符串强制转换为文本数组。
After much fiddling with delicate postgres syntax, I found a good middle way with active record:
在对精致的 postgres 语法进行了大量摆弄之后，我找到了一个使用 active record 的好方法：
def change
change_column :users, :event_location, :text, array: true, default: [], using: "(string_to_array(event_location, ','))"
end
The only direct postgresql there is the (string_to_array() )function call. Here are the docs on that--note that you have to supply a delimiter.
唯一直接的 postgresql 是(string_to_array() )函数调用。这是有关该文档的文档 --注意您必须提供分隔符。
回答by tolbard
Using Rails 4.2 on postgresql 9.4 with a down and a up, base on lrrthomas response. Note: your starting column should have a default of nil
基于 lrrthomas 响应，在 postgresql 9.4 上使用 Rails 4.2 并带有向下和向上。注意：你的起始列应该有一个默认值 nil
class ChangeEmailAndNumberColumnForContact < ActiveRecord::Migration
def up
change_column :contacts, :mobile_number, :text, array: true, default: [], using: "(string_to_array(mobile_number, ','))"
change_column :contacts, :email, :text, array: true, default: [], using: "(string_to_array(email, ','))"
end
def down
change_column :contacts, :mobile_number, :text, array: false, default: nil, using: "(array_to_string(mobile_number, ','))"
change_column :contacts, :email, :text, array: false, default: nil, using: "(array_to_string(email, ','))"
end
end
回答by Veeru
def change
change_column :table, :dummy_column, :string, array: true, default: '{}'
end
Notice:
注意：
it's specified as data type :string with array: true to default the column to an empty array ( [] ), you use default: '{}'
它被指定为数据类型 :string with array: true 将列默认为空数组 ( [] )，您使用默认值：'{}'
回答by Lakshya Garg
It can be done like below:
它可以像下面那样完成：
change_column :table, :column, :string, array: true, default: {}, using: "(string_to_array(column, ','))"
