remove-enum-value.sql

-- context: we have a table `animals` with a column `animal_type`
-- this column has an enum value of ('human', 'cat', 'dog') - a.k.a. `possible_animal_types`
-- we want to remove the 'dog' possibility from the enum type
-- this is a simplified case too; my actual code is much longer as i'm using this type in a few tables
-- is there a better/easier way??

alter table animals
  alter column animal_type type text,
  alter column animal_type set default 'human';

drop type possible_animal_types;

create type possible_animal_types as enum ('human', 'cat');

alter table animals
  alter column animal_type set default 'human'::possible_animal_types;
alter table animals
  alter column animal_type type possible_animal_types using animal_type::possible_animal_type;