Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Renaming an ENUM label in PostgreSQL
/*
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;
@d11wtq

This comment has been minimized.

Copy link
Owner Author

d11wtq commented Oct 26, 2012

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

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

This comment has been minimized.

Copy link

joevandyk commented Mar 14, 2014

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.

@a0s

This comment has been minimized.

Copy link

a0s commented Sep 12, 2017

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.

@ludwinas

This comment has been minimized.

Copy link

ludwinas commented Nov 1, 2018

@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 :)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.