Geek Answers Handbook

Postgres: default value for column (row) cannot be automatically added for enumeration input

I have a status column in a table that I want to list. I originally created this field as an integer, thinking that I would use the built-in Rails rename functions. It turns out that at least Rails 4.1 is required, but I'm using 4.0, and the upgrade process will take some time.

But thinking about how it all works, I realized that I could have either an ActiveRecord enumeration or a postgres enumeration, and not both. I thought that in the long run it would be best to have a more explicit enumeration of postgres. So, I wrote a transition to convert the status column from an integer to an enumeration.

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

But I get this error:

 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 searches tells me that it should work , but it is not. I thought maybe the problem is that I just can't go from integer to enumeration. So be it. My solution was to convert the column to a string first, and then try to convert it to an enumeration.

 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"

And this gives me the following error:

 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

This led me to believe that it was somehow related to the default value, so I tried to specify the default value in the change_column :

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

This successfully changes the column to a row with a default value of "pending", but change_column fails with the same default error for the column.

I understand that I could just delete the column together and then recreate it exactly the way I want, but at the moment this is a matter of posterity. Why can't heck convert a column from an integer or row to an enumeration? Is anyone

ACCEPTED UPDATE UPDATE

Based on Gary's answer, 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
+6
source share
1 answer

You need to remove the default value from the column before the change, because the default value is set, valid for the old type of the column, but incompatible with the new type.

 alter table schema.site_applications alter status drop default

Then you can change the type of the column. Finally, as soon as the new column type is applied, you can add a new default to the table.

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

More articles:


All Articles