Skip to content

Instantly share code, notes, and snippets.

@cenyG
Forked from sevlyar/migration.sql
Created August 24, 2018 14:16
Show Gist options
  • Save cenyG/b2e68e451c6bb1379fba8d9625c1d620 to your computer and use it in GitHub Desktop.
Save cenyG/b2e68e451c6bb1379fba8d9625c1d620 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