'Alter column type with non-trivial usage of "using" clause
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
