Postgres: Default for column (string) cannot be cast automatically to type enum

Question

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.

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"

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 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"

And that 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

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"

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?

UPDATE WITH ACCEPTED ANSWER

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

Can you show the results of a \dt site_applications in PSQL. Because the message sounds like there is a default value set against the column. You might need to remove the default while you make the change and then add back a default of the appropriate type afterwards. — Gary - Stand with Ukraine
– Gary - Stand with Ukraine, Commented Jul 22, 2015 at 18:07
@Gary I think you are absolutely right, but I can't for the life of me figure out what type of default to set so that it can be switched to an enum. But, I did the \dt schema.site_applications and this is what it returned: schema | site_applications | table | eliduke — Eli Duke
– Eli Duke, Commented Jul 22, 2015 at 19:03
Oh, ok. Here's that: status | integer | not null default 0. But I'm not surprised by that, really, because when I tried to convert the integer to enum, I got error cannot cast type integer to status_options. So, I thought that since an enum is sort of variant of a string that I would first convert it to a string and then convert to enum. And THAT is when I got the default for column error. At one point I even tried converting the integer to a string and setting the default on the string column to nil and then converting to enum. That failed too with the same error. — Eli Duke
– Eli Duke, Commented Jul 23, 2015 at 14:32
In that case you want to issue a alter table schema.site_applications alter status drop default to remove the default entirely. convert the type, then alter table schema.site_applications alter status set default 'pending'::status_options' not entirely sure of the default enum syntax. — Gary - Stand with Ukraine
– Gary - Stand with Ukraine, Commented Jul 23, 2015 at 15:22

Gary - Stand with Ukraine · Accepted Answer · 2015-07-23 16:15:13Z

32

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

answered Jul 23, 2015 at 16:15

Gary - Stand with Ukraine

7,6291 gold badge33 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres: Default for column (string) cannot be cast automatically to type enum

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related