Alter column type with non-trivial usage of "using" clause-CodePudding

Assume we have this table named "mytable":

name [varchar]	eating_habits [int]
Anna	1
Roland	3
Sepp	1
Katrin	2
Lukas	4
Hedwig	3

Now I realize I want to change the colum vegetarian to be specific. I create my own enum type:

CREATE TYPE diet AS ENUM ('vegetarian', 'vegan', 'omni');

What I want is to change the type of the column "eating_habits" to "diet". To do this I also want to map between the types like this

1 --> 'vegan'
2 --> 'vegetarian'
rest --> 'omni'

How would I go about this? I know that one can use the USING clause like this:

ALTER TABLE mytable
  ALTER COLUMN eating_habits TYPE diet
  USING (
    <Expression>
  )

But I can't figure out what "Expression" should be, and the vast majority of examples online are trivial casts.

CodePudding user response：

You need a CASE expression:

ALTER TABLE mytable
  ALTER COLUMN eating_habits TYPE diet
  USING ( 
      case eating_habits 
        when 1 then 'vegan'::diet 
        when 2 then 'vegetarian'::diet 
        else 'omni'::diet
      end
  )