Database

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.

Creating enums

You can define a Postgres Enum using the create type statement. Here's an example:


_10
create 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:


_10
create table person (
_10
id serial primary key,
_10
name text,
_10
current_mood mood
_10
);

Here, the 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:


_10
insert into person
_10
(name, current_mood)
_10
values
_10
('Alice', 'happy');

Querying data with enums

When querying data, you can filter and compare Enum values as usual:


_10
select *
_10
from person
_10
where current_mood = 'sad';

Managing enums

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:


_10
update person
_10
set current_mood = 'excited'
_10
where 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, content:


_10
alter 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.

Getting a list of enum values

Check your existing Enum values by querying the enum_range function:


_10
select enum_range(null::mood);

Resources