Upgrading an int column to enum type in postgresql

Question

Following this question I was wondering if there as in elegant way to do the same for int values.

More precisely given an Integer column with a fixed number of values (not necessarily contiguous), how would I go about mapping each number to each enum value. And when I say map I mean migrate

For example: Lets assume the enum is as

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

We would like to map:

0 -> 'sad'
1 -> 'ok'
10 -> 'happy'

Where, let's say, we have table which contains a column called mood with values {0,1,10} only.

Also, I can't see that the answer here helps me.

I use Postgres 9.5

Answer 1

The USING clause is an expression to tell postgresql how to transform the value. This is an expression just as you might use in SELECT. So if you need to specify a mapping then you can just use a CASE statement.

alter table foo 
  alter bar type mood 
  using 
     case bar 
        when 0 then 'sad' 
        when 1 then 'ok' 
        when 10 then 'happy' 
     end :: mood;