Enums vs Check Constraints in Postgres

A question recently came up in the internal Crunchy Data slack channel: Does anyone actually use enums out in the wild?

Enums in Postgres

If you're unfamiliar with enums, they’re enumerated types, a static set of values in a database like days of the week or a shipping status. Enums are a powerful feature of Postgres that allows you to define a set of predefined values that can be assigned to a column. However, enums can have some limitations and drawbacks that make them less than ideal for certain scenarios.

Let's look at a practical example for enums. Here we create a table with order_status as pending, shipped, or cancelled.

CREATE TYPE order_status AS ENUM (
  'pending',
  'shipped',
  'cancelled'
);

CREATE TABLE orders (
    id serial,
    status order_status,
    created_at timestamp default now(),
    updated_at timestamp default now(),
    tracking_id text
);

As we can see here, using enums allows us to have very specific values stored and other features and functions securly built off of those static values. Enums let you have the exact value you want without having to join to a smaller lookup table. And you haven't allowed freeform text in this field that can be prone to typos or errors.

Check constraints

So if enums are handy, then why in our internal chat was there little to no examples of them being actually used out in the wild. Because this handy little feature check constraints allows you the same functionality in Postgres with even more flexibility.

Constraints allow developers to specify rules and restrictions for the values that can be inserted into a column, and can be easily modified or removed without having to change the underlying data structure. Using constraints instead of enums, we can simply define a CHECK constraint that specifies the allowed values for the status column. This allows us to easily add or remove values as needed, without having to modify the data structure or update existing data. We can also define complex rules and restrictions, such as allowing multiple statuses for a single order, or requiring that certain statuses be set in combination with others.

CREATE TABLE orders (
    id serial,
    status text CHECK (status IN ('pending', 'shipped', 'cancelled')),
    created_at timestamp DEFAULT now(),
    updated_at timestamp DEFAULT now(),
    tracking_id text
);

Check constraints can do even more, for instance we can ensure that updated_at is always greater than or equal to created_at. We can ensure that when status is shipped that we have a tracking_id that is NOT NULL.

So while they're incredibly useful as another approach to enums, they're broadly an amazing tool for ensuring data quality. By leveraging the power of constraints, you can easily enforce data integrity and ensure that the database is able to adapt to changing requirements and scenarios.

My vote, if you’re thinking about enums, do a test drive of the CHECK constraint.