How to Create an Enum Type in PostgreSQL

PostgreSQL is a powerful relational database that supports various data types, one of which is ENUM. If you’ve ever needed to store predefined values in a table—like statuses, categories, or roles—ENUM can be a great choice. It makes queries cleaner, prevents invalid data entry, and improves performance. Let’s dive into how you can create and use ENUM types in PostgreSQL with simple examples.

What is an ENUM Type?

An ENUM (short for "enumerated") is a data type that consists of a fixed set of values. Unlike a VARCHAR or TEXT column, which allows any string, an ENUM type only permits values explicitly defined during creation. This makes data more structured and prevents incorrect entries.

Example Use Case

Let’s say we have a users table, and we want to store the role of each user. The possible roles are:

• Admin
• Editor
• Viewer

Instead of using a VARCHAR column, where someone might accidentally enter admin, Admin, or administrator, we can enforce consistency with an ENUM type.

Creating an ENUM Type in PostgreSQL

To define an ENUM type in PostgreSQL, use the CREATE TYPE statement:

CREATE TYPE user_role AS ENUM ('Admin', 'Editor', 'Viewer');

This creates a new ENUM type called user_role with three possible values.

Using ENUM in a Table

Once the ENUM type is created, you can use it in a table:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    role user_role NOT NULL
);

Now, the role column in the users table only allows values 'Admin', 'Editor', or 'Viewer'. Any other value will result in an error.

Inserting Data into the ENUM Column

INSERT INTO users (name, role) VALUES ('Shubham', 'Admin');
INSERT INTO users (name, role) VALUES ('Ashish', 'Editor');
INSERT INTO users (name, role) VALUES ('Aman', 'Viewer');

If you try to insert an invalid role, PostgreSQL will reject it:

INSERT INTO users (name, role) VALUES ('Kajal', 'SuperAdmin');
-- ERROR: invalid input value for enum user_role: "SuperAdmin"

Querying ENUM Data

Fetching users based on their role is straightforward:

SELECT * FROM users WHERE role = 'Admin';

PostgreSQL stores ENUM values efficiently, so queries involving ENUMs tend to be faster than those involving text comparisons.

Modifying an ENUM Type

If you need to add a new role, you can use ALTER TYPE:

ALTER TYPE user_role ADD VALUE 'SuperAdmin';

Note: You can only add values at the end of the list. If you need a more flexible approach, consider using a lookup table instead of ENUM.

Conclusion

ENUM types in PostgreSQL help keep your data structured and prevent invalid values. They are ideal for fixed, predefined categories like user roles, order statuses, or payment methods. However, they are not always the best choice if your data is highly dynamic.