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

Philip Couling · Accepted Answer · 2016-09-04 11:05:55Z

7

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;

answered Sep 4, 2016 at 11:05

Philip Couling

15.2k8 gold badges73 silver badges104 bronze badges

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

Collectives™ on Stack Overflow

Upgrading an int column to enum type in postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related