-
-
Save crashbell/c7767d583d239ea0a63b1e61d0f5cbf9 to your computer and use it in GitHub Desktop.
Renaming an ENUM label in PostgreSQL
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| 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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment