Dealing with Enum Type in PostgreSQL
Tags: database, postgres, beginners
Published: 2021-12-15 06:14:42


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.
```sql
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:
```sql
ALTER TYPE delivery_status RENAME TO delivery_status_1;
```

### Get Enum Values
To get list of enum values, use following query
```sql
SELECT UNNEST(enum_range(null::delivery_status)) 
AS delivery_status;
```
This is the result after creating `delivery_status` enum above
![postgresql enum query result](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oauxwzx39tryn6j04ued.png)
 

### 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
```sql
ALTER TYPE delivery_status ADD VALUE 'OTHERS';
```

It is also possible to add value before or after specific value.
```sql
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.
![postgresql update add enum value](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wellzo6y6hwrrvpvny1p.png)
 
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.

```sql
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
```sql
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.
```sql
-- 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`
![posgtresql change enum value](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qh9m6sy4av3vck8krmou.png)

After removing `IN_TRANSIT` value
![postgresql remove enum value](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hq53mpj7n4wlwkcl167b.png)
 

## Delete enum type
To delete enum type and all of it values, execute following query
```sql
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.