Postgres:不能自动将列(字符串)的默认类型强制转换为键入枚举 [英] Postgres: Default for column (string) cannot be cast automatically to type enum

问题描述

我想成为枚举的表中有一个 status 列.最初，我以整数形式创建该字段，以为我会使用内置的Rails枚举功能.事实证明，这至少需要Rails 4.1，但我使用的是4.0，并且升级过程将需要一些时间.

I have a status column in a table that I want to be an enum. Originally I created that field as an integer, thinking that I would use the built in Rails enum functionality. Turns out that requires at least Rails 4.1, but I am using 4.0 and the process of upgrading is going to take some time.

但是考虑到这一切是如何工作的，我意识到我可以 一个ActiveRecord枚举或一个postgres枚举，但不能同时使用.我认为从长远来看，最好使用更明确的postgres枚举.因此，我编写了一个迁移，将 status 列从整数转换为枚举.

But thinking about how this all works, I realized that I can have either an ActiveRecord enum or a postgres enum, not both. I thought that in the long term having a more explicit postgres enum would be best. So, I wrote a migration to convert the status column from an integer to an enum.

execute "CREATE TYPE status_options AS ENUM ('pending', 'declined', 'approved');"
change_column :site_applications, :status, "status_options USING status::status_options"

但是，我收到此错误:

PG::CannotCoerce: ERROR:  cannot cast type integer to status_options
ALTER TABLE "site_applications" ALTER COLUMN "status" TYPE status_options USING status::status_options

到目前为止，我在搜索结果中看到的所有内容都告诉我应该有用，但事实并非如此.我以为也许是我不能从整数到枚举.随它吧.我的解决方案是先将列转换为字符串，然后然后尝试将其转换为枚举.

Everything that I have seen so far in my searchings tells me that should have worked, but it doesn't. I thought maybe the problem is that I just can't go from integer to enum. So be it. My solution was to first convert the column to a string and then try to convert it to enum.

change_column :site_applications, :status, :string
execute "CREATE TYPE status_options AS ENUM ('pending', 'declined', 'approved');"
change_column :site_applications, :status, "status_options USING status::status_options"

这给了我以下错误:

PG::DatatypeMismatch: ERROR:  default for column "status" cannot be cast automatically to type status_options
ALTER TABLE "site_applications" ALTER COLUMN "status" TYPE status_options USING status::status_options

这使我相信这与默认值有关，因此我尝试在 change_column 声明中指定默认值:

That led me to believe that this had something to do with the default value, so I tried specifying the default in the change_column declaration:

change_column :site_applications, :status, :string, default: "pending"

该操作成功将列更改为默认值为"pending"的字符串，但 change_column 失败，并出现相同的默认列"错误.

That successfully changes the column to a string with a default of "pending", but change_column fails with the same "default for column" error.

我意识到我可以简单地将所有列放在一起，然后按照自己的意愿重新创建它，但是这只是后代的问题.为什么我无法将列从整数或字符串转换为枚举?有人吗?

I realize that I could simply drop the column all together and then recreate it exactly how I want, but at this point it's a matter of posterity. Why the heck can't I convert a column from integer or string to enum? Anyone?

使用接受的答案进行更新

根据加里(Gary)的回答，这是可行的迁移.

Based on Gary's answer down there, this is the migration that worked.

def up
  execute "ALTER TABLE site_applications ALTER status DROP DEFAULT;"
  execute "CREATE TYPE status_options AS ENUM ('pending', 'declined', 'approved');"
  change_column :site_applications, :status, "status_options USING status::status_options", default: "pending"
end

def down
  change_column :site_applications, :status, :string, default: "pending"
  execute "DROP TYPE status_options;"
end

推荐答案

您需要在更改之前从列中删除默认值，因为默认值设置为对旧列类型有效但与以下列不兼容的值新类型.

You need to remove the default value from the column prior to the change as the default is set to a value that is valid for the old column type but incompatible with the new type.

alter table schema.site_applications alter status drop default

然后您可以更改列类型.最后，一旦应用了新的列类型，就可以在表中添加新的默认值.

Then you can change the column type. Finally once the new column type is applied, you can add a new default against the table.

alter table schema.site_applications alter status set default 'pending'::status_options

这篇关于Postgres:不能自动将列(字符串)的默认类型强制转换为键入枚举的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！