Intro to Postgres enums

In most programming languages the use of enums is pretty common and heavily relied upon by developers. It took me quite some time to learn that this feature exists in the database realm as well.
Therefore, I will provide a short introductory summary of enumerated types in Postgres.

Enums are used for representing a limited number of possible values.
Status fields are a good example.
Let’s say we want to store account information inside a database table:

In our example an account can have one of the following status:

• ACTIVE
• DELETED
• BANNED

We can create a database enum by executing:

CREATE TYPE ACCOUNT_STATUS AS ENUM ( 'ACTIVE', 'DELETED', 'BANNED');

We can then use the generated enum in our table definition:

CREATE TABLE ACCOUNT (STATUS ACCOUNT_STATUS, USERNAME TEXT, EMAIL TEXT);

Now we insert a new account:

INSERT INTO ACCOUNT VALUES ('ACTIVE', 'testuser','user@test.com');

Since we set the datatype of the STATUS table to ACCOUNT_STATUS the database now takes care of validation.

If we try to insert an account with status INACTIVE

INSERT INTO ACCOUNT VALUES ('INACTIVE', 'testuser', 'user@test.com');

The database returns the following error:

ERROR: invalid input value for enum account_status: "INACTIVE"

Since "INACTIVE" is not valid for our defined enum.

Having a data validation mechanism like this on database level helps to enforce data integrity at all times.

Querying enums

Unfortunately the values of enum types can not be retrieved by the following query:

SELECT * FROM ACCOUNT_STATUS

But one can make use of the built-in functions UNNEST and ENUM_RANGE

SELECT UNNEST(ENUM_RANGE(NULL::ACCOUNT_STATUS))

This query returns one row for each enum value.
In our example:

• ACTIVE
• INACTIVE
• BANNED

Alternatives to enums: check constraints

We can add a similar data validation mechanism to our database tables by adding a check constraint.
In our example the constraint would look like this:

CREATE TABLE ACCOUNT (STATUS TEXT CHECK (STATUS IN ('ACTIVE', 'INACTIVE', 'BANNED')), USERNAME TEXT, EMAIL TEXT);

After defining the column name and type we add the constraint by using the CHECK keyword.
In the parenthesis following CHECK we define a boolean expression which must be true for every row of the table.
For better maintainability we can also give the check constraint a name:

CREATE TABLE ACCOUNT (STATUS TEXT CONSTRAINT ACCOUNT_STATUS_CONSTRAINT CHECK (STATUS IN ('ACTIVE', 'INACTIVE', 'BANNED')), USERNAME TEXT, EMAIL TEXT);

Enum vs Check constraint

Advantages of enums

Once defined enums can be reused in different columns or tables.
Additionally, the usage of enums helps with understanding the database schema.
Because creation of a specific type conveys more purpose: e.g. column of type ACCOUNT_STATUS is more specific than a column of type TEXT.

Disadvantages of enums

With enums we defined a new data type for our column (note the CREATE TYPE syntax above).
Therefore, the built-in string operators and functions do not work on enums.
With a check constraint do not have to define our own data type, so any built-in operator and function for the used column data type can be used.

Like in other parts of software development, designing a database schema involves tradeoffs.
So it is quite handy to have different tools in your tool belt!

If you like this summary, feel free to follow me on Twitter or here for more content.

Sources:
Postgres docs