Postgresql remove value from enum

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

Connect to your PostgreSQL database using a client tool such as pgAdmin or psql.
Assuming we have an existing enum type called “color” with values ‘red’, ‘green’, and ‘blue’, you can use the following SQL statement to remove a value:

    
      ALTER TYPE color DROP VALUE 'green';

This statement will remove the value ‘green’ from the enum type ‘color’.

After executing the above SQL statement, the enum type ‘color’ will only have the values ‘red’ and ‘blue’.

Keep in mind that removing a value from an enum type can have implications for any existing data that references the removed value. If there are existing rows in a table that reference the removed value, the references will become invalid. You may need to update the affected rows before removing the value.

Here’s an example to illustrate the process:

    
      -- Create the enum type
      CREATE TYPE color AS ENUM ('red', 'green', 'blue');
      
      -- Create a table using the enum type
      CREATE TABLE products (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100),
        color color
      );
      
      -- Insert some data
      INSERT INTO products (name, color) VALUES ('Product 1', 'red'), ('Product 2', 'green'), ('Product 3', 'blue');
      
      -- Remove the 'green' value from the enum type
      ALTER TYPE color DROP VALUE 'green';
      
      -- Invalidates rows that reference the removed 'green' value
      -- Update affected rows to use a valid value
      UPDATE products SET color = 'red' WHERE color = 'green';
      
      -- Confirm the value has been removed from the enum type
      SELECT enum_range(NULL::color);

In the example above, we create an enum type ‘color’ with three values (‘red’, ‘green’, ‘blue’). We then create a table ‘products’ that has a column ‘color’ of type ‘color’. Some sample data is inserted into the table.

We then remove the value ‘green’ from the enum type using the ALTER TYPE statement. This action invalidates the existing row that refers to ‘green’. Therefore, we update the affected row to use a valid value (‘red’ in this case).

Finally, we confirm the removal of the ‘green’ value by selecting the range of the ‘color’ enum type.

Note that it’s recommended to back up your database before making any significant changes like removing values from an enum type.

Leave a comment Cancel reply