Why can't I convert a column from one enum to another?-CodePudding

Given this setup

create type myenum_one as enum('foo', 'bar', 'baz');

create table mytable (
  myvalue myenum_one
);

alter table mytable
add constraint mycheckconstraint check (myvalue != 'bar');

insert into mytable values ('foo');

create type myenum_two as enum('foo', 'bar');

It then fails on when trying to alter the column type

alter table mytable
alter column myvalue type myenum_two using myvalue::text::myenum_two;

With error

ERROR: operator does not exist: enum_two <> enum_one
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

CodePudding user response：

You need to drop the check constraint before altering the column type, and the recreate it after (if it's still relevant), like so

alter table mytable
drop constraint mycheckconstraint;

alter table mytable
alter column myvalue type myenum_two using myvalue::text::myenum_two;

alter table mytable
add constraint mycheckconstraint check (myvalue != 'bar');