Managing Enums in Postgres
Enums in Postgres are a custom data type. They allow you to define a set of values (or labels) that a column can hold. They are useful when you have a fixed set of possible values for a column.
You can define a Postgres Enum using the
create type statement. Here's an example:
_10create type mood as enum (_10'happy',_10'sad',_10'excited',_10'calm'_10);
In this example, we've created an Enum called "mood" with four possible values.
When to use Enums#
There is a lot of overlap between Enums and foreign keys. Both can be used to define a set of values for a column. However, there are some advantages to using Enums:
- Performance: You can query a single table instead of finding the value from a lookup table.
- Simplicity: Generally the SQL is easier to read and write.
There are also some disadvantages to using Enums:
- Limited Flexibility: Adding and removing values requires modifying the database schema (i.e.: using migrations) rather than adding data to a table.
- Maintenance Overhead: Enum types require ongoing maintenance. If your application's requirements change frequently, maintaining enums can become burdensome.
In general you should only use Enums when the list of values is small, fixed, and unlikely to change often. Things like "a list of continents" or "a list of departments" are good candidates for Enums.
Using Enums in tables#
To use the Enum in a table, you can define a column with the Enum type. For example:
_10create table person (_10id serial primary key,_10name text,_10current_mood mood_10);
current_mood column can only have values from the "mood" Enum.
Inserting Data with Enums#
You can insert data into a table with Enum columns by specifying one of the Enum values:
_10insert into person_10(name, current_mood)_10values_10('Alice', 'happy');
Querying Data with Enums#
When querying data, you can filter and compare Enum values as usual:
_10select *_10from person_10where current_mood = 'sad';
You can manage your Enums using the
alter type statement. Here are some examples:
Updating Enum Values#
You can update the value of an Enum column:
_10update person_10set current_mood = 'excited'_10where name = 'Alice';
Adding Enum Values#
To add new values to an existing Postgres Enum, you can use the
ALTER TYPE statement. Here's how you can do it:
Let's say you have an existing Enum called
mood, and you want to add a new value,
_10alter type mood add value 'content';
Removing Enum Values#
Even though it is possible, it is unsafe to remove enum values once they have been created. It's better to leave the enum value in place.
Read the Postgres mailing list for more information:
There is no
ALTER TYPE DELETE VALUE in Postgres. Even if you delete every occurrence of an Enum value within a table (and vacuumed away those rows), the target value could still exist in upper index pages. If you delete the
pg_enum entry you'll break the index.
Getting a list of Enum Values#
Check your existing Enum values by querying the enum_range function:
- Official Postgres Docs: Enumerated Types