The Art of PostgreSQL/

...

/

Enum

Enum

Learn what you can do with the enum data type in PostgreSQL.

We'll cover the following...

Relational database

The enum data type has been added to PostgreSQL to make it easier to support migrations from MySQL. The proper relational design would use a reference table and a foreign key instead:

PostgreSQL
create table color(id serial primary key, name text);
create table cars
 (
   brand   text,
   model   text,
   color   integer references color(id)
 );
insert into color(name)
     values ('blue'), ('red'),
            ('gray'), ('black');
insert into cars(brand, model, color)
     select brand, model, color.id
      from (
            values('ferrari', 'testarosa', 'red'),
                  ('aston martin', 'db2', 'blue'),
                  ('bentley', 'mulsanne', 'gray'),
                  ('ford', 'T', 'black')
           )
             as data(brand, model, color)
           join color on color.name = data.color;

In this setup, the table color lists available colors to choose from, and the cars table registers the availability of a model from a brand in a given color. It’s possible to make ...