PosgreSQL small task: ENUM and ENUM inserts

Today is the time to talk about the great ENUM type.

The problem is the following, you need to categorize all your animals (aka beasts) by its moods in every moment.

So you come out with this easy to make table:

Simple Table (alternative 1)

And it’s a good first step, but imagine that you are not in a great humor for spelling (meaning Saturday night) and your partying mood tells to insert instead of this:

This variant of “Cool”:

Well, that’s kind of “cool” but not for your database as you’ll need a proper “cool” in order to make some reports of the important moods of your beasts.

So, in order to make yourself more accountable to your moods, you generate this more restrictive version to only have 6 options of humor moods:

Table with Checks (alternative 2)

The main problem with this check constraint is that to reuse it in other tables, you have to copy it manually every time. And it’s very expensive to modify the check constraint.

Enter normalization. Let’s now think about normal forms. Yes, you’re thinking of improving the majesty of your database and you know that normal forms are trendy things for majestic endeavors.

So you come out with this lookup table:

Obviously you need to insert this kind of values in order to preserve your hard defined mood taxonomy.

And then the Table needs to be something like this:

Normalized Table (alternative 3)

What is the problem with that? well check that out soon.

Entering the ENUM

Somebody told you that you need to use ENUM, maybe an article in Medium?. So you came with this nice ENUM type to save the day. No normalization, no ids, no nothing. Just a “type” of fun.

Don’t forget to update your table to reflect the new type. Easy right?

Simple Table with ENUM (alternative 4)

Let’s check under the hood. What happens when doing a lot of inserts in the 3 types of tables “beast_humor” already defined. Well, these are the results for the first 3 options.

You can see that your normalization, as good as it is, in terms of insert performance is a bummer. Also the check constraint doesn’t have any penalty vs not having the constraint. Meaning?, it’s a guarantee without performance impact (wow!). But what happen with ENUM?, here’s it:

Comparison of Alternatives 2,3 and 4.

No significant difference with the text check in terms of performance, and it is easier to maintain. It gives the same guarantees to validate values as the check constraint, and it’s reusable, hence, the same advantage of the normalized lookup table. In the next article (PostgreSQL task) we’ll check other potentialities of the ENUM. So, stay tuned.

Thanks to Boriss Mejías for providing all the base for this article. Follow him @Tchorix