CODEWITHSUNDEEP
postgresqlenumsalter-column
Lorentz Lasson
Lorentz Lasson

Reputation: 1185

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

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.

Upvotes: 0

Views: 523

Answers (1)

Lorentz Lasson
Lorentz Lasson

Reputation: 1185

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

Upvotes: 1

Related Questions