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

Issue

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.

Solution

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
  )

Answered By – a_horse_with_no_name

Answer Checked By – Senaida (AngularFixing Volunteer)

Leave a Reply

Your email address will not be published.