This is a brief guide for effectively understanding and working with enum type in Postgres: Why does it exist? What does it do? How to work with it?
There is boolean data type to define a binary column in PostgreSQL. Kind of yes/no switch. For example,
is_active column is commonly used to describe whether a record is active.
But what if there are more than two options? Another data type is needed to handle the use case. That data type also has to be strict to prevent other values being written. Then enum comes to the rescue.
Enum type is best solution to describe state data. It basically define states of a record in PostgreSQL. For example, an e-commerce platform needs to track package delivery status. The package is either in packaging process, waiting for pick-up, in delivery, in transit, or already delivered to customer. It can be formalized as five states:
DELIVERED. Enum can handle this, in a way that will be described below.
Enum type has following behaviours:
- It is treated as type in PostgreSQL
- It applies to all tables in same schema
- It has static values
- Enum value is case-sensitive
- Enum value size is 4 bytes
- Each enum value is unique (duplicate is not allowed)
- Adding enum value requires ALTER operation
- Order of enum values matter when querying
Use enum if:
- Cheap constraint is preferrable
- Enum values won't change a lot in the future
- Enum values aren't too many. Personally, I'd say less than 10 as rule of thumb.
- Enum values have different priority
- Avoiding invalid value is necessary
Otherwise, other solutions like reference table or even simple integer type will suffice.
In this article, delivery status example above will be used. Below is general query syntax to create enum type.
CREATE TYPE delivery_status AS ENUM ('PACKAGING', 'WAITING_PICKUP', 'IN_DELIVERY', 'IN_TRANSIT', 'DELIVERED');
One can rename enum type by using following query:
ALTER TYPE delivery_status RENAME TO delivery_status_1;
To get list of enum values, use following query
SELECT UNNEST(enum_range(null::delivery_status)) AS delivery_status;
As the product evolves, one realizes that package can be lost, broken, stolen, etc. New package state is needed. Let's name it
OTHERS to describe unexpected situations that may happen to a package.
As of version 9.1, PostgreSQL support adding enum value to do just that. Just execute query below
ALTER TYPE delivery_status ADD VALUE 'OTHERS';
It is also possible to add value before or after specific value.
ALTER TYPE delivery_status ADD VALUE 'OTHERS' BEFORE 'DELIVERED'; ALTER TYPE delivery_status ADD VALUE 'OTHERS' AFTER 'IN_TRANSIT';
Prior to version 9.1, things were more complicated. A popular workaround consists of three steps:
- Rename enum type to something else
- Create new enum with correct type name and value
- Delete old enum type.
ALTER TYPE delivery_status RENAME TO delivery_status_old; CREATE TYPE delivery_status AS ENUM ('PACKAGING', 'WAITING_PICKUP', 'IN_DELIVERY', 'IN_TRANSIT', 'DELIVERED', 'OTHERS'); DROP TYPE delivery_status_old;
As of version 10, PostgreSQL support renaming enum value
ALTER TYPE delivery_status RENAME VALUE 'PACKAGING' TO 'PREPARING';
Other than that, PostgreSQL doesn't support changing or deleting specific enum value. The workaround is the same as above: rename old type, create new and correct type, and delete old type.
-- change PACKAGING to PREAPRING ALTER TYPE delivery_status RENAME TO delivery_status_old; CREATE TYPE delivery_status AS ENUM ('PREPARING', 'WAITING_PICKUP', 'IN_DELIVERY', 'IN_TRANSIT', 'DELIVERED', 'OTHERS'); DROP TYPE delivery_status_old; -- delete IN_TRANSIT value ALTER TYPE delivery_status RENAME TO delivery_status_old; CREATE TYPE delivery_status AS ENUM ('PREPARING', 'WAITING_PICKUP', 'IN_DELIVERY', 'DELIVERED', 'OTHERS'); DROP TYPE delivery_status_old;
To delete enum type and all of it values, execute following query
DROP TYPE delivery_status;
Enum type is useful tool to ensure type safety in PostgreSQL. I personally use it to make sure system flow doesn't mess up. I find this short article is useful to remind me important things about enum in PostgreSQL. I hope it helps you as well, dear readers.
If you find other tips to deal with enum types in PostgreSQL, please let me know in comments.
Stay sharp and curious.