Postgres Enums dissected

PostgreSQL is a popular open-source object-relational database management system known for its stability, performance and feature-rich SQL implementation.

Postgres enums (short for "enumerated types") represent one special data type in PostgreSQL that allows you to define a list of possible values for a column. They can be used to enforce data integrity and make it easier to query and sort data.

Let's sky dive through them to see what they have to offer and how to best use them.

What are Postgres enums for?

In PostgreSQL, an enum is a data type that consists of a set of symbolic names (enumerators) and their values.

Enums are created using the CREATE TYPE command and can be used in a table column just like any other data type (e.g. integer, varchar).

Enums have some important differences from other data types:

Enum values are stored as integers, with the enumerator names serving as aliases for the integer values.
Enums have a fixed internal storage size, which is the size needed to store the largest enumerator. This means that enums may take up more space than other data types (e.g. varchar) in some cases.
Although Enums were originally fixed – meaning you could not add or remove values once you defined the set of enumerators – Postgres 9.1 released in 2011 enabled the ALTER function, as shown here:

Keep in mind however that when you alter an enum type, all columns that use this specific enum type will also be affected. It is therefore important to update all relevant code and scripts as to avoid any potential backfiring.

How to create and use Postgres enums?

To create an enum type in PostgreSQL, you can use the following syntax:

Here is an example of creating an enum type called "status" with the possible values "active", "inactive", and "deleted":

To use an enum type in a table, you can specify it as the data type for a column:

Enums also have a number of specific functions and operators that you can use when querying data. For example:

enum_range() returns an array of all enumerators for an enum type, in the order they were defined.
enum_first() returns the first enumerator for an enum type.
enum_last() returns the last enumerator for an enum type.

Postgres enums cons

As mentioned earlier, enums are fixed and cannot be altered once created. In other words, they are not flexible.

This can be a disadvantage if you need to add or remove values from an enum type.

Furthermore, if you need to change the values of an enum type (e.g. renaming an enumerator), you will need to create a new enum type, migrate the data to the new type, and then update any references to the old enum type. This can be a time-consuming process.

Best practices for using Postgres enums

It is important to keep your enums organized and maintain them properly to avoid issues down the road. Here are some tips:

Choose meaningful names for your enumerators
Avoid using abbreviations or acronyms that may not be immediately clear
Use enum ranges to define groups of related enumerators (e.g. "pending", "approved", "rejected" for a "status" enum)
Consider using a tool or script to automate the process of creating and updating enums

Considerations for using Postgres enums in relation to other data types:

Enums may be more suitable for small lists of fixed values (e.g. "male" or "female" for a "gender" column)
For more extensive lists or sets of values that may need to be updated or changed more frequently, other data types (e.g. varchar, integer) may be more appropriate.
Be aware of the potential for increased storage size when using enums, especially if you have many enumerators with long names.

In summary, enums are a useful data type in PostgreSQL that can help improve data integrity, facilitate querying and sorting, and potentially improve performance.

However, it is important to consider the limitations and potential challenges of using Postgres enums, and to choose the data type that is relevant to your needs.

Who are we?

Forest Admin is an admin panel solution that saves your back-end engineers time and gives your operational teams more autonomy. Our highly customizable admin panel connects to your databases and APIs to ease your operations so that you can focus more on your business and less on backend operations.

If you have any doubts, here's a quick view of the features Forest Admin brings to the table

Data: Forest Admin's standout feature is its ability to scan your data structure and automatically generate an operational admin panel.

Customization: Once the initial admin panel has been auto-generated, Forest Admin enables you to tailor its functionality to match your precise needs, making the tool adaptable for a wide array of business applications.

Workspace: The dream tool to manage your daily operations. This drag and drop feature enables you to create workflows at will in order to ease your workload. The rule is simple: one workflow, one workspace.

Dashboard: Customize your own dashboard to take a quick look at key metrics.

Integration Capabilities: Forest Admin can be installed in two ways: connecting it to an existing application built on frameworks like Node.js, Django, Laravel, Ruby on Rails, or linking it directly to one or multiple databases – check all of our integrations on our integration page.