Skip to content

Instantly share code, notes, and snippets.

@sevlyar
Created August 26, 2016 13:18
Show Gist options
  • Save sevlyar/8e02f2267a5037ff1590c321b2331366 to your computer and use it in GitHub Desktop.
Save sevlyar/8e02f2267a5037ff1590c321b2331366 to your computer and use it in GitHub Desktop.
Add postgresql enum's element in transaction
-- rename the old enum
ALTER TYPE country_code RENAME TO country_code__;
-- create the new enum with the old name
CREATE TYPE country_code AS ENUM ('DE', 'AT', 'NL', 'CH');
-- ALTER all you enum columns
ALTER TABLE user_address
ALTER COLUMN country DROP DEFAULT;
ALTER TABLE user_address
ALTER COLUMN country TYPE country_code
USING country::text::country_code; -- use old value -> to string -> to new enum
ALTER TABLE user_address
ALTER COLUMN country SET DEFAULT 'DE';
-- drop the old enum
DROP TYPE country_code__;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment