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

Question

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.

score 1 · Accepted Answer · answered Apr 08 '22 at 10:50

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');

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

1 Answers1