Changing the definition of a data type

PostgreSQL comes with several data types, but users can create custom types to most 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 covers only 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 cookbook_author AS ENUM ('Hannu','Simon');

The other popular case is composite data types, which are created in this way:

CREATE TYPE node AS (
  node_name text,
  connstr text,
  standbys text[]);

How to do it…

Say you are upgrading your database to edition, or version, 2.0 and you need a new value for the enumerative type that we defined in the preceding code. You want to put the new value in a certain position to preserve the alphabetical ordering. For that, you can use an ALTER TYPE syntax, like this:

ALTER TYPE cookbook_author...