How to Delete an Enum Type Value in PostgreSQL: Step-by-Step Guide to Removing 'moderator' from Your Enum List

PostgreSQL’s ENUM data type is a powerful tool for enforcing data integrity by restricting column values to a predefined set of options. For example, you might use an enum like user_role with values 'admin', 'moderator', and 'user' to ensure only valid roles are stored in a users table. However, over time, enum values can become obsolete—perhaps the 'moderator' role is no longer needed, or your team has merged it into another role.

Deleting an enum value in PostgreSQL isn’t as straightforward as adding one (which can be done with ALTER TYPE ... ADD VALUE). As of PostgreSQL 16, there’s no built-in DROP VALUE command for enums. Instead, you’ll need a workaround involving creating a new enum, migrating data, and updating dependencies.

This guide walks you through the entire process of safely removing 'moderator' from an enum, with clear examples and best practices to avoid data loss or schema issues.

Table of Contents#

  1. What is a PostgreSQL Enum?
  2. Why Delete an Enum Value?
  3. Prerequisites
  4. Step-by-Step Guide to Removing 'moderator'
  5. Troubleshooting Common Issues
  6. Conclusion
  7. References

What is a PostgreSQL Enum?#

An ENUM (enumerated type) in PostgreSQL is a user-defined data type that restricts column values to a fixed list of allowed options. Enums improve data consistency by preventing invalid values (e.g., 'guest' in a user_role enum that only allows 'admin', 'moderator', or 'user').

Example enum creation:

CREATE TYPE user_role AS ENUM ('admin', 'moderator', 'user');

You might use this enum in a users table:

CREATE TABLE users (  
  id SERIAL PRIMARY KEY,  
  username VARCHAR(50) UNIQUE NOT NULL,  
  role user_role NOT NULL DEFAULT 'user'  
);

Why Delete an Enum Value?#

Over time, enum values may become obsolete. For example:

  • The 'moderator' role is merged into 'admin', making it redundant.
  • Business requirements change, and 'moderator' is no longer used.
  • The enum has grown cluttered, and removing unused values improves schema clarity.

PostgreSQL does not natively support dropping enum values (as of versions 12–16). To remove 'moderator', we’ll use a safe workaround involving creating a new enum, migrating data, and updating dependencies.

Prerequisites#

Before starting, ensure you have:

  • PostgreSQL Access: Either via psql (command-line) or a GUI tool (e.g., pgAdmin).
  • Privileges: CREATE TYPE, ALTER TABLE, and DROP TYPE permissions on the database.
  • Backup: A recent backup of your database (to revert if something goes wrong).
  • Dependency Awareness: Knowledge of which tables/columns use the enum (e.g., users.role).

Step-by-Step Guide to Removing 'moderator'#

Let’s assume we have an enum user_role with values ('admin', 'moderator', 'user') and a users table using this enum. We’ll remove 'moderator' safely.

Step 1: Understand the Current Enum Structure#

First, confirm the enum’s name, values, and description.

Using psql:#

Run \dT+ user_role to list details about the user_role enum:

List of data types  
Schema |   Name    | Description | OID | Type | Size | Elements | Owner | Access privileges | Description  
--------+-----------+-------------+-----+------+------+----------+-------+-------------------+-------------  
public | user_role |             | 123 | enum | 4    | admin, moderator, user | alice |                   |

Using SQL (for GUI tools):#

Query PostgreSQL’s system catalogs to list enum values:

SELECT e.enumlabel AS enum_value  
FROM pg_type t  
JOIN pg_enum e ON t.oid = e.enumtypid  
WHERE t.typname = 'user_role'  
ORDER BY e.enumsortorder;

Output:

enum_value  
------------  
admin  
moderator  
user  
(3 rows)

Step 2: Check Dependencies and Clean Up Data#

Before modifying the enum, identify:

  1. Columns using the enum: Which tables/columns rely on user_role?
  2. Existing data: Are there rows with role = 'moderator'?
  3. External dependencies: Triggers, constraints, or application code referencing 'moderator'?

2.1 Find Columns Using the Enum#

Query information_schema.columns to list dependent columns:

SELECT table_name, column_name  
FROM information_schema.columns  
WHERE udt_name = 'user_role'; -- udt_name = enum type name

Example output:

table_name | column_name  
------------+-------------  
users      | role  
(1 row)

2.2 Update or Delete Rows with 'moderator'#

If there are rows with role = 'moderator', update them to a valid value (e.g., 'admin') or delete them. Failing to do this will break later steps.

Check for 'moderator' rows:

SELECT COUNT(*) AS moderator_count  
FROM users  
WHERE role = 'moderator';

If moderator_count > 0, update the rows:

-- Replace 'admin' with your desired fallback value  
UPDATE users  
SET role = 'admin'  
WHERE role = 'moderator';

2.3 Check for External Dependencies#

Enums may be referenced in triggers, views, or application code. For example:

  • A trigger that logs 'moderator' actions.
  • An app UI that displays 'moderator' as an option.

Fix these dependencies first (e.g., update triggers, remove UI references) to avoid errors post-migration.

Step 3: Create a New Enum Without 'moderator'#

PostgreSQL does not support ALTER TYPE user_role DROP VALUE 'moderator' (as of version 16). Instead, create a new enum excluding 'moderator':

CREATE TYPE user_role_new AS ENUM ('admin', 'user'); -- No 'moderator'

Verify the new enum:

SELECT unnest(enum_range(NULL::user_role_new)) AS new_enum_values;

Output:

new_enum_values  
-----------------  
admin  
user  
(2 rows)

Step 4: Update Dependent Columns to Use the New Enum#

Next, alter the column(s) using user_role to use user_role_new instead.

For the users.role column:

ALTER TABLE users  
ALTER COLUMN role TYPE user_role_new  
USING role::text::user_role_new; -- Convert old enum to text, then to new enum

How It Works:#

  • role::text: Casts the old user_role value to a string (e.g., 'admin').
  • ::user_role_new: Casts the string to the new enum (only allowed if the string is in user_role_new).

Step 5: Validate the Changes#

Confirm the column now uses the new enum and 'moderator' is gone.

Check Column Type:#

SELECT column_name, udt_name  
FROM information_schema.columns  
WHERE table_name = 'users' AND column_name = 'role';

Output:

column_name | udt_name  
-------------+-----------  
role        | user_role_new  
(1 row)

Check for Remaining 'moderator' Values:#

SELECT DISTINCT role FROM users;

Output (should only show admin and user):

role  
-------  
admin  
user  
(2 rows)

Step 6: Drop the Old Enum Type#

Once confident the new enum works, drop the obsolete user_role:

DROP TYPE user_role;

If DROP TYPE Fails:#

If you get an error like cannot drop type user_role because other objects depend on it, there are unresolved dependencies. Use pg_depend to find them:

SELECT obj_description(classid::regclass) AS dependent_object  
FROM pg_depend  
WHERE refclassid = 'pg_type'::regclass  
  AND refobjid = (SELECT oid FROM pg_type WHERE typname = 'user_role');

Fix the dependencies (e.g., drop triggers, views) and retry DROP TYPE.

Step 7: (Optional) Rename the New Enum to Match the Original#

If you want the enum to retain its original name (user_role), rename user_role_new:

ALTER TYPE user_role_new RENAME TO user_role;

Verify the rename:

SELECT typname FROM pg_type WHERE typname = 'user_role';

Output:

typname  
-----------  
user_role  
(1 row)

Troubleshooting Common Issues#

Issue 1: "Cannot alter column ... because it has dependent objects"#

Cause: Triggers, constraints, or indexes reference the enum column.
Fix: Drop or update dependencies first (e.g., DROP TRIGGER IF EXISTS log_role_changes ON users;).

Issue 2: "invalid input value for enum user_role_new: 'moderator'"#

Cause: Rows with 'moderator' were not updated before altering the column.
Fix: Re-run the update query from Step 2.2:

UPDATE users SET role = 'admin' WHERE role::text = 'moderator';

Issue 3: "permission denied to drop type user_role"#

Cause: Insufficient privileges.
Fix: Ensure your user has DROP TYPE permissions (ask your DBA to grant them).

Conclusion#

Removing an enum value in PostgreSQL requires a workaround, but it’s manageable with careful planning:

  1. Understand the enum’s current structure and dependencies.
  2. Clean up data (update/delete rows with the obsolete value).
  3. Create a new enum without the value, migrate columns, and drop the old enum.

Always back up your database before schema changes, and validate dependencies to avoid downtime. Future PostgreSQL versions may add native DROP VALUE support, but this guide works for all versions up to 16.

References#

2025-11