Search⌘ K
Home
Courses
The Art of PostgreSQL

Enum

Explore the enum data type in PostgreSQL, its role in supporting migrations from MySQL, and when to use enums versus reference tables. Understand how to create enum types, perform queries, and maintain data consistency while managing denormalized data.

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 ...