Renaming an ENUM label in PostgreSQL

enum.sql

/*
 Assuming you have an enum type like this.
 You want to rename 'pending' to 'lodged'
 */
CREATE TYPE dispute_status AS ENUM('pending', 'resolved', 'open', 'cancelled');

BEGIN;

  ALTER TYPE dispute_status ADD VALUE 'lodged';
  UPDATE dispute SET status = 'lodged' WHERE status = 'pending';

  /* if it was a default value on the column, also do this */
  ALTER TABLE disputes ALTER COLUMN status SET DEFAULT 'lodged';

  /* Make a new temporary type with the enum you want */
  CREATE TYPE dispute_status_new AS ENUM('lodged', 'resolved', 'open', 'cancelled');

  /* Migrate to the new type, specifying the cast to a string */
  ALTER TABLE disputes ALTER COLUMN status SET DATA TYPE dispute_status_new USING status::text;

  /* Switch the types over */
  DROP TYPE dispute_status;
  ALTER TYPE dispute_status_new RENAME TO dispute_status;

COMMIT;

Or the easy way (albeit 'officially' unsupported):

UPDATE pg_enum SET enumlabel = 'lodged' WHERE enumtypid = 'dispute_status'::regtype AND enumlabel = 'pending';

You could also do something like:

alter table disputes alter column status type dispute_status_new using
    case status 
      when 'lodged' then 'pending' 
      else status::text::dispute_status_new 
    end;

No need for the update.

ALTER TYPE ... ADD cannot run inside a transaction block

Another way: create another column with new type of enum, migrate data from old enum to new enum, drop old column and rename column and enum.

@a0s since ALTER TYPE ... ADD indeed cannot be run inside a transaction block, I've solved it in a different way, namely by changing the column data type temporarily to TEXT, dropping the type, creating a new type and setting the data type back :)

In PostgreSQL 10, ALTER TABLE ... RENAME VALUE could be used:

ALTER TYPE dispute_status RENAME VALUE 'pending' TO 'lodged';