How to Use ENUM in PostgreSQL

Introduction

ENUM or enumerated types in PostgreSQL are user-defined data types that allow you to create a column with a value restricted to a set of predefined constants. This tutorial will guide you through the use of ENUM in PostgreSQL with practical examples.

Creating Enums

To define an ENUM type, you can use the CREATE TYPE command followed by the enumeration’s name and the list of possible values:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

Now, you can create a table that uses this mood type:

CREATE TABLE person (
    name VARCHAR(50),
    current_mood mood
);

Inserting Data with Enums

Inserting data into the person table is straightforward:

INSERT INTO person (name, current_mood) VALUES ('John', 'happy');

Selecting Enum Fields

To query an ENUM column, you just use the standard SELECT statement:

SELECT * FROM person WHERE current_mood = 'happy';

Updating Enum Values

You can also update ENUM fields like any other column:

UPDATE person SET current_mood = 'sad' WHERE name = 'John';

Adding New Enum Values

If you need to expand the range of the ENUM type, you can use the ALTER TYPE command:

ALTER TYPE mood ADD VALUE 'elated' AFTER 'happy';

Comparing Enum Values

In PostgreSQL, you can compare two ENUM values based on the order they are defined in:

SELECT name FROM person WHERE current_mood > 'ok';

Removing Enum Types

When ENUM types are no longer needed, you may remove them with:

DROP TYPE mood;

Note that you need to remove all columns and functions that use the ENUM type before you can drop it.

Advanced Enum Usage

You can also create complex behaviors by integrating ENUM with functions and other PostgreSQL features:

CREATE OR REPLACE FUNCTION mood_check(m mood) RETURNS BOOLEAN AS $
BEGIN
    IF m = 'sad' THEN
        RETURN FALSE;
    ELSE
        RETURN TRUE;
    END IF;
END;
$ LANGUAGE plpgsql IMMUTABLE;

SELECT name, mood_check(current_mood) FROM person;

This function analyzes the mood and returns a boolean value based on the mood evaluation.

Enum Constraints and Best Practices

While ENUMs offer clear advantages, such as ensuring data integrity and clarity, they also come with disadvantages. Consider the need for schema modifications over time and the cost of such updates when deciding to implement ENUMs in your database design.

Conclusion

In this tutorial, we’ve explored PostgreSQL ENUMs from basics to advanced examples. They help maintain data consistency and offer a more descriptive context. However, be mindful of the rigidity they introduce and consider your long-term schema evolution before using ENUMs extensively.