Skip to content

Instantly share code, notes, and snippets.

@hoangmy92

hoangmy92/enum.sql

Forked from d11wtq/enum.sql
Created July 26, 2017 06:36
Show Gist options
  • Save hoangmy92/b41d63dd26d2d353824a6e27764863f5 to your computer and use it in GitHub Desktop.
Save hoangmy92/b41d63dd26d2d353824a6e27764863f5 to your computer and use it in GitHub Desktop.
Download ZIP
Renaming an ENUM label in PostgreSQL
Raw
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment