An In-Depth Guide to Using Enums in PostgreSQL

As a PostgreSQL database administrator, enums (short for enumerated types) are an extremely useful data type that allow you to restrict a column to accept only a specific set of predefined values. In this comprehensive guide, we‘ll cover everything you need to know about enums in PostgreSQL – from what they are, to why you may want to use them, to real-world examples of how to implement them effectively.

What is an Enumerated Type in PostgreSQL?

An enum in PostgreSQL is a custom data type that represents a static, ordered set of values. Think of it like a custom string type that can only be set to values you specify within the enum definition.

For example, let‘s say you‘re building a coloring book app that allows users to color cartoon characters. You may create a color enum like this:

CREATE TYPE color AS ENUM (‘red‘, ‘blue‘, ‘green‘, ‘yellow‘, ‘purple‘);

Now the color type can only ever be set to one of those allowed values. This makes enums ideal for representing a fixed set of choices, like categories, statuses, roles, etc.

Some key properties that make PostgreSQL enums so useful:

• Enums enforce data integrity – The database will prevent inserts or updates with invalid enum values that aren‘t defined in the enum. This ensures bad data doesn‘t get into the system.
• Enums save space – An enum value is stored in just 4 bytes on disk, compared to a minimum of one byte per character for string types. Less data equals improved performance.
• Enums make queries easier – You can check equality for an enum column in a WHERE clause using untyped string literals.
• Enums are index-able – Like any other data type, you can speed up queries by indexing enum columns.
• Enums are simple to use – ANSI SQL compliant syntax makes enums easy to create, alter, query and migrate between PostgreSQL databases.

In summary, enums increase data integrity while providing simplicity and performance improvements.

Enum Use Cases

Here are some common examples of when using a PostgreSQL enum makes sense:

Fixed Product Categories

CREATE TYPE category AS ENUM (‘electronics‘, ‘clothing‘, ‘housewares‘);  

CREATE TABLE products (
  name text,
  price decimal,
  category category
);

User Account Statuses

CREATE TYPE account_status AS ENUM (‘active‘, ‘closed‘, ‘inactive‘);

CREATE TABLE users (
  name text,
  status account_status
);

Task Priorities

CREATE TYPE priority AS ENUM (‘low‘, ‘medium‘, ‘high‘);

CREATE TABLE tasks (
  title text, 
  priority priority
);

Order Statuses

CREATE TYPE order_status AS ENUM (‘open‘, ‘shipped‘, ‘delivered‘, ‘returned‘, ‘canceled‘);

CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  status order_status
);

Any time you need to restrict a string field to a strictly defined list of possible values, an enum should be considered in PostgreSQL.

The benefit over just using a regular string datatype is that enums guarantee only valid values can exist through enforcing constraint checks on inserts and updates. Enums also require less storage and allow easier querying against the exact set of values.

Creating and Managing Enums

Let‘s go through examples of how to create, update, query and delete PostgreSQL enums.

Creating Enums

Enumerated types are created using the CREATE TYPE SQL command. The basic syntax is:

CREATE TYPE type_name AS ENUM (‘value1‘,‘value2‘,‘value3‘);

To build on the product category example above, we would create that enum using:

CREATE TYPE category AS ENUM (‘electronics‘, ‘clothing‘, ‘housewares‘);

Enums are typed, so you must first create the type before you can use it in a column definition like:

CREATE TABLE products (
  category category
);

Once an enum type has been defined, any future table columns created with that type can only accept one of the defined enum values.

Altering Enums

You may occasionally need to modify an existing enum type by adding, renaming or removing values. This can be done using the ALTER TYPE command.

For example, to add a new category value of ‘kitchen‘ to the category enum:

ALTER TYPE category ADD VALUE ‘kitchen‘;

Values can also be renamed by explicitly setting a new value after the one being changed:

ALTER TYPE category RENAME VALUE ‘housewares‘ TO ‘household‘; 

And values no longer needed can be dropped with:

ALTER TYPE category DROP VALUE ‘clothing‘;

The big advantage over dropping and re-creating the enum is that ALTER TYPE avoids having to migrate data to a new column definition. Existing values are left unchanged.

One restriction is you can only add values to the end of an enum definition, not the middle or beginning.

Typecast Syntax

Typecasts allow you convert another data type or string literal into a defined enum value. The basic syntax is:

ENUM_COLUMN :: enum_type

For example:

SELECT 
  ‘electronics‘ :: category AS electronics, 
  ‘kitchen‘ :: category AS kitchen; 

This allows flexible value assignments without relying solely on hardcoded enum values. Typecasts are especially useful for converting user input or values from other systems.

Querying Enums

To check the defined values for an enum type, you can simply query the pg_enum system catalog:

SELECT enumlabel  
FROM pg_enum
WHERE enumtypid = ‘category‘::regtype;

Which would display:

enumlabel
-------------- 
electronics
household  
kitchen

This provides a handy way to get a list of valid values that can be used when building application code.

Enum columns can be queried, filtered and ordered the same as any other data type column. For example:

SELECT  
  product_id,
  name,
  category
FROM products
WHERE category = ‘electronics‘
ORDER BY category ASC;

And of course enums can be joined, aggregated, and otherwise used like typical columns as well.

Dropping Enums

When an enumerated type is no longer needed, you can remove it with:

DROP TYPE my_enum;

However, dropping an enum requires that there are no remaining table columns still dependent on that type. You would first need to either drop or alter existing columns using the enum.

Attempting to drop an enum still actively used would result in an error like:

ERROR:  cannot drop type my_enum because other objects depend on it
DETAIL:  column my_table.my_column depends on type my_enum
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

So use enum drops carefully and beware of cascading effects across your database schema.

Enum Usage Tips

Here are some helpful tips for working with PostgreSQL enums based on experience using them in production:

• Lean towards using snake case versus camel case for enum names i.e. order_status over orderStatus. Keeps them visually distinct from table names and CamelCase types.
• Prefix enum names with the context of how they are used (e.g. user_role, payment_status) for easy understanding across a large schema.
• Use enums over hardcoded check constraints lists for better flexibility to evolve values over time.
• Be careful when modifying enums on production databases (especially removing values) that could break applications temporarily expecting certain values to exist.
• Avoid creating monster enums with dozens of values???break it down into separate types instead for sanity.
• Document business meaning/use cases for each enum value in the PostgreSQL comment attribute or a data dictionary to prevent confusion on what vague values like ‘unknown‘ actually mean.

When Not to Use Enums

While enum types are fantastic for many use cases, they aren‘t a magic bullet either. Here are some scenarios where using regular string columns make more sense:

• Column values cannot be strictly defined upfront (dynamic or fast changing domains).
• Require storing values not predefined e.g. customer names or comment text.
• Need to store and filter by partial string matches and fuzzy comparisons. Matching enum labels exactly can be more restrictive in some cases.
• Ordered range of numeric values???better to use integer or even a foreign key to a values table instead for this use case.
• Future column changes could be highly disruptive (e.g. commonly used types). Might be safer to keep values flexible.

So consider scope, volatility and overhead before defaulting to an enum as opposed to normal string storage.

Summary

Enumerated types are an invaluable tool for restricting values stored in string-like columns to an approved, predefined list of options.

Implementing PostgreSQL enums improves data integrity, reduces storage needs, and simplifies querying against categorical values.

Define enum types using CREATE TYPE and optionally ALTER TYPE, then set column definitions to enforce only allowing the enumerated values to be saved.

I hope this comprehensive guide gives you confidence to start utilizing enums more in your own PostgreSQL database schemas right away! Reach out if you have any other questions.