Changing the definition of an enum data type
PostgreSQL comes with several data types, but users can create custom types to faithfully represent any value. Data type management is mostly, but not exclusively, a developer’s job, and data type design goes beyond the scope of this book. This is a quick recipe that only covers the simpler problem of the need to apply a specific change to an existing data type.
Getting ready
Enumerative data types are defined like this:
CREATE TYPE satellites_urani AS ENUM ('titania','oberon');
The other popular case is composite data types, which are created as follows:
CREATE TYPE node AS
( node_name text,
connstr text,
standbys text[]);
How to do it…
If you misspelled some enumerative values, but you realized it too late, you can fix it like so:
ALTER TYPE satellites_urani RENAME VALUE 'titania' TO 'Titania';
ALTER TYPE satellites_urani RENAME VALUE 'oberon...