Altering the type of a column in postgres from one enum type to another requires an intermediate cast to\u00a0text<\/span>. Additionally, if there is a default value for the field, it cannot be altered and instead must be dropped and re-added as the new type.<\/p>\n

Simple case: New type contains all old-type values<\/h2>\n

In the simple version, the new enum type contains (at least) all of the same labels\u00a0as the old one. For instance, as with these two:<\/p>\n

CREATE TYPE old_enum AS ENUM ('a', 'b', 'c', 'd');\r\nCREATE TYPE new_enum AS ENUM ('a', 'b', 'c', 'd', 'e');<\/pre>\n
No default on column<\/h3>\n
If a table has an old_enum<\/span>\u00a0column and we want to turn it into a new_enum<\/span>\u00a0one, with no default value in place on the column, we can use the following command:<\/p>\n
ALTER TABLE table_name\r\n  ALTER COLUMN column_name\r\n    SET DATA TYPE new_enum\r\n    USING column_name::text::new_enum;<\/pre>\n
The USING<\/span>\u00a0expression casts the current value of column_name<\/span>\u00a0to text<\/span>, and then to new_enum<\/span>. This works because\u00a0every allowed value of the first enum type exists in the second.<\/p>\n
With a default value<\/h3>\n
This case is not significantly more difficult to deal with. If there is a default value on the column, we simply remove it before altering the enum type of the column, and then add a new one when we’re done:<\/p>\n
ALTER TABLE table_name\r\n  ALTER COLUMN column_name DROP DEFAULT,\r\n  ALTER COLUMN column_name\r\n    SET DATA TYPE new_enum\r\n    USING column_name::text::new_enum,\r\n  ALTER COLUMN column_name SET DEFAULT 'a';\r\n<\/pre>\n
Converting enum\u00a0labels<\/h2>\n
A more complicated scenario arises when not all of the old labels appear in the new enum. I’ll assume that there is a mapping from the old ones to the new, at least for every label that is known to appear in a row of the table. If there isn’t, then the conversion is probably not a good idea in the first place.<\/p>\n
Consider now an even newer type,<\/p>\n
CREATE TYPE newer_enum AS ENUM ('alpha', 'beta', 'c', 'd', 'e');<\/pre>\n
We still want to convert from the old_enum<\/span>\u00a0type, but now we also want to map the label ‘a’ to ‘alpha’, and ‘b’ to ‘beta’, while leaving ‘c’ and ‘d’ alone. This can be accomplished by manually applying each required change via a CASE<\/span>\u00a0 statement in the USING<\/span>\u00a0 expression:<\/p>\n
ALTER TABLE table_name\r\n  ALTER COLUMN column_name DROP DEFAULT,\r\n  ALTER COLUMN column_name\r\n    SET DATA TYPE newer_enum\r\n    USING (\r\n      CASE column_name::text\r\n        WHEN 'a' THEN 'alpha'\r\n        WHEN 'b' THEN 'beta'\r\n        ELSE column_name::text\r\n      END CASE\r\n    )::newer_enum,\r\n  ALTER COLUMN column_name SET DEFAULT 'alpha';<\/pre>\n
For each record, this statement returns ‘alpha’ or ‘beta’ if the column contains ‘a’ or ‘b’, respectively, or otherwise returns the current value in the column cast as text. The returned value in all cases is then cast to the newer enum type.<\/p>\n","protected":false},"excerpt":{"rendered":"
Altering the type of a column in postgres from one enum type to another requires an intermediate cast to\u00a0text. Additionally, if there is a default value for the field, it cannot be altered and instead must be dropped and re-added as the new type. Simple case: New type contains all old-type values In the simple … [Read more…]<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/www.munderwood.ca\/index.php\/wp-json\/wp\/v2\/posts\/44"}],"collection":[{"href":"https:\/\/www.munderwood.ca\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.munderwood.ca\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.munderwood.ca\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.munderwood.ca\/index.php\/wp-json\/wp\/v2\/comments?post=44"}],"version-history":[{"count":4,"href":"https:\/\/www.munderwood.ca\/index.php\/wp-json\/wp\/v2\/posts\/44\/revisions"}],"predecessor-version":[{"id":92,"href":"https:\/\/www.munderwood.ca\/index.php\/wp-json\/wp\/v2\/posts\/44\/revisions\/92"}],"wp:attachment":[{"href":"https:\/\/www.munderwood.ca\/index.php\/wp-json\/wp\/v2\/media?parent=44"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.munderwood.ca\/index.php\/wp-json\/wp\/v2\/categories?post=44"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.munderwood.ca\/index.php\/wp-json\/wp\/v2\/tags?post=44"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}