crashbell · August 7, 2017 07:08 · Oct 26, 2012
diff --git a/enum.sql b/enum.sql
@@ -0,0 +1,25 @@
+/*
+ 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;