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
CREATE TYPE order_status AS ENUM ( 'pending', 'shipped', 'cancelled' ); CREATE TABLE order ( 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.
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.
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 order ( 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
is always greater than or equal to
created_at. We can ensure that when status
shipped that we have a
tracking_id that is
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
December 8, 2022 •More by this author