Dealing with Enum Type in PostgreSQL

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?

Why Does Enum Type Exist?

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.

What Does Enum Type Do Well?

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: PACKAGING, WAITING_PICKUP, IN_DELIVERY, IN_TRANSIT, and 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

When to Use Enum

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.

Enum Operations

Create Enum Type

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');

Rename Enum Type

One can rename enum type by using following query:

ALTER TYPE delivery_status RENAME TO delivery_status_1;

Get Enum Values

To get list of enum values, use following query

SELECT UNNEST(enum_range(null::delivery_status)) 
AS delivery_status;

This is the result after creating delivery_status enum above

Add Enum Value

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';

The result is the same, new value in enum type.

Prior to version 9.1, things were more complicated. A popular workaround consists of three steps:

1. Rename enum type to something else
2. Create new enum with correct type name and value
3. 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;

Change or delete enum value

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;

After changing enum value PACKAGING to PREPARING

After removing IN_TRANSIT value

Delete enum type

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.