Rails Migration更改列以使用Postgres数组 [英] Rails Migration changing column to use Postgres arrays

问题描述

我正在尝试更改数据库中的列，以便它可以使用Postgres数组数据类型. 当前，表格列的类型为字符串.

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.

我正在使用以下迁移对其进行转换:

I am using the following migration to convert it:

def change
  change_column :table, :dummy_column, :text, array: true, default: []
end

但是出现以下错误:

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

推荐答案

PostgreSQL不知道如何自动将varchar的列转换为varchar的数组.它不知道您可能打算做什么，因为它无法知道您认为当前值所采用的格式.

PostgreSQL doesn't know how to automatically convert a column of varchar into 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.

所以您需要告诉它；这就是USING子句的作用.

So you need to tell it; that's what the USING clause is for.

ActiveRecord似乎没有明确支持USING子句(这并不奇怪，因为它几乎不支持最基本的数据库功能).不过，您可以为迁移指定自己的SQL文本.

ActiveRecord doesn't seem to explicitly support the USING clause (not surprising, as it barely supports even the most basic database features). You can specify your own SQL text for the migration, though.

假设您的字符串用逗号分隔，并且本身可能不包含逗号，例如:

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

(我自己并没有使用过Rails，也没有进行测试，但这与其他示例中使用的语法一致).

(I don't use Rails myself and haven't tested this, but it's consistent with the syntax used in examples elsewhere).

这篇关于Rails Migration更改列以使用Postgres数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！