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

Question

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 column "eating_habits" 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.

user330315 · Accepted Answer · 2022-03-03 14:07:56Z

6

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 Mar 3, 2022 at 14:07

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related