Postgres remove value from enum

by

To remove a value from an enum type in PostgreSQL, you need to follow these steps:

  1. Start by creating a new enum type without the value you want to remove. This is done using the
    ALTER TYPE command with the ADD VALUE clause. For example, if you have an enum named ‘status’
    with four values (‘active’, ‘inactive’, ‘pending’, ‘deleted’) and you want to remove the ‘deleted’ value, you can execute
    the following SQL statement:

    ALTER TYPE status ADD VALUE 'old_deleted' AFTER 'pending';

    This command adds a new value ‘old_deleted’ after the ‘pending’ value. This is needed to prevent any conflicts when
    updating the existing data that has the ‘deleted’ value.

  2. Update the existing data to replace the ‘deleted’ value with the new value you added in the previous step.
    This can be achieved with a simple UPDATE statement. For example, if you have a table named ‘users’ with a
    column named ‘status’ storing the enum values, you can execute the following SQL statement:

    UPDATE users SET status = 'old_deleted' WHERE status = 'deleted';

    This statement updates all rows where the ‘status’ column has the value ‘deleted’ and replaces it with ‘old_deleted’.

  3. Finally, you can drop the old value from the enum type using the ALTER TYPE command with the
    RENAME VALUE clause. For example, to remove the ‘deleted’ value from the ‘status’ enum type, you can execute
    the following SQL statement:

    ALTER TYPE status RENAME VALUE 'old_deleted' TO 'deleted';

    This command renames the ‘old_deleted’ value to ‘deleted’, effectively removing the ‘deleted’ value from the enum type.

Here’s a complete example to illustrate the process: 

CREATE TYPE status AS ENUM ('active', 'inactive', 'pending', 'deleted');

ALTER TYPE status ADD VALUE 'old_deleted' AFTER 'pending';

UPDATE users SET status = 'old_deleted' WHERE status = 'deleted';

ALTER TYPE status RENAME VALUE 'old_deleted' TO 'deleted';

Leave a comment