Skip to content

Instantly share code, notes, and snippets.

@ludwinas ludwinas/enum.sql
Last active Nov 1, 2018

Embed
What would you like to do?
Renaming an ENUM value in Postgres 9.5
-- assuming your existing type is as follows
CREATE TYPE user_types AS ENUM
( 'user',
'admin',
'foo'
);
-- here's how you rename the 'foo' value to 'bar'
BEGIN;
ALTER TABLE users ALTER COLUMN user_type SET DATA TYPE TEXT;
-- if the column has a default value
ALTER TABLE users ALTER COLUMN user_type SET DEFAULT 'user';
DROP TYPE user_types;
UPDATE users SET user_type = 'bar' WHERE user_type = 'foo';
CREATE TYPE user_types AS ENUM
( 'user',
'admin',
'bar'
);
-- once more, if the column has a default value change the default before changing the column
-- type, else Postgres won't know how to convert the existing default value to the new type
ALTER TABLE users ALTER COLUMN user_type SET DEFAULT 'user'::user_types;
ALTER TABLE users ALTER COLUMN user_type SET DATA TYPE user_types USING user_type::user_types;
COMMIT;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.