Dec 8, 2021 by Robert Gravelle

In software development, there is a Boolean data type for working with binary states. Hence, it only has two possible states: true and false. However, there exists a third state that must often be accounted for, and that is one for "none of the above" or simply "other". In relational databases, NULL might seem to be a good candidate for this state, but is not, due to its historical context. Recall from previous blogs that NULL has a very specific meaning in Structured Query Language (SQL) to indicate that a data value does not exist in the database. The NULL value was actually introduced by none other than the creator of the relational database model himself, E. F. Codd. In SQL, NULL has come to indicate "missing and/or inapplicable information". Seen in this light, NULL can hardly represent a "none of the above" or "other" condition. So then, what is the best way to represent ternary - or three-state - data in relational databases? We will answer that question here today for MySQL and PostgreSQL. Next week we'll cover SQL Server and Oracle.

Introducing Enumerated Types

Enumerated Types - also known as Enums - are data types that contain a static, ordered set of values. Enums are ideal for storing things such as the days of the week, user preferences, and any other collection of related data that seldom change. Having enjoyed support in a number of programming languages for decades, some of the biggest relational database players, including MySQL and PostgreSQL, have also introduced the Enum type. Unfortunately, there are a few holdouts, including SQL Server and Oracle, which we'll talk about next week.

Creating and Using Enums in MySQL

To get an idea how one would use Enums, let's start with the number one relational database in the world. Yes, I speak of MySQL. As you can see in the following CREATE TABLE statement, designating a column as an Enum type is quite trivial:

CREATE TABLE shirts (
  name VARCHAR(40),
  size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);

From there, you can refer to an Enum using one of its string values:

INSERT INTO shirts (name, size) 
VALUES ('dress shirt','large'), 
       ('t-shirt','medium'),
       ('polo shirt','small');
  
SELECT name, size FROM shirts WHERE size = 'medium';

UPDATE shirts SET size = 'small' WHERE size = 'large';

With regards to the tri-state issue, we can implement one as follows:

CREATE TABLE employee (
  name VARCHAR(50),
  security_clearance ENUM('enhanced', 'secret', 'none')
);

Now, trying to insert an invalid value into an Enum column will result in an error and will fail:

Creating and Using Enums in PostgreSQL

In PostgreSQL, Enum types are created using the CREATE TYPE command:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

Once created, the Enum type can be used in table much like any other type:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
    name text,
    current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
 name | current_mood 
------+--------------
 Moe  | happy
(1 row)

Conclusion

In today's blog, we saw how to represent tri-state data, and other discreet values, in MySQL and PostgreSQL, using Enumerated Types. But what about other database types? Do they not support tri-state data? They do, but using different data types. We'll explore these next week.