Thread: Modifying an existing table to use an ENUM instead of an int

Modifying an existing table to use an ENUM instead of an int

From

Mike Christensen

Date:

07 June 2010, 04:17:07

Hi all -

I have an existing table that looks like this:

CREATE TABLE orders
(
  --Bunch of stuff you don't care about
  orderstate integer NOT NULL,
  --Etc
)

with a bunch of data in it.  I've now created this new data type:

CREATE TYPE OrderStateEnum AS ENUM ('Preview', 'InQueue', 'Ordered',
'Error', 'Cancelled');

I want to change the type of "orderstate" from integer to
OrderStateEnum, and cast 0 to Preview, 1 to InQueue, 2 to Ordered,
etc.

I could create a new column, copy all the data over, then delete the
old column, but I suspect there's some cool way to go about doing
this.  Thanks!

Mike

PS - This is totally shameless, but if you cook or your spouse does,
I'd totally appreciate it if you could take a short survey to help us
develop this website we're working on.  We're hoping to get about a
thousand responses so I have to plug it everywhere :)  This URL is
http://survey.kitchenpc.com/

Re: Modifying an existing table to use an ENUM instead of an int

From

Mike Christensen

Date:

07 June 2010, 04:40:31

Ok, I'm convinced this should work but it does not:

CREATE FUNCTION ConvertIntToOrderStateEnum(state integer) RETURNS
OrderStateEnum AS
$BODY$
SELECT CASE WHEN $1=0 then 'Preview'::OrderStateEnum
   WHEN $1=1 then 'InQueue'::OrderStateEnum
   WHEN $1=2 then 'Ordered'::OrderStateEnum
   WHEN $1=3 then 'Error'::OrderStateEnum
   WHEN $1=4 then 'Cancelled'::OrderStateEnum ELSE NULL END;
$BODY$
LANGUAGE 'sql' IMMUTABLE STRICT;

ALTER TABLE orders ALTER orderstate TYPE OrderStateEnum
USING ConvertIntToOrderStateEnum(OrderState);

I get the error:

ERROR: operator does not exist: orderstateenum >= integer
SQL state: 42883
Hint: No operator matches the given name and argument type(s). You
might need to add explicit type casts.

On Mon, Jun 7, 2010 at 12:11 AM, Mike Christensen <mike@kitchenpc.com> wrote:
> Hi all -
>
> I have an existing table that looks like this:
>
> CREATE TABLE orders
> (
>  --Bunch of stuff you don't care about
>  orderstate integer NOT NULL,
>  --Etc
> )
>
> with a bunch of data in it.  I've now created this new data type:
>
> CREATE TYPE OrderStateEnum AS ENUM ('Preview', 'InQueue', 'Ordered',
> 'Error', 'Cancelled');
>
> I want to change the type of "orderstate" from integer to
> OrderStateEnum, and cast 0 to Preview, 1 to InQueue, 2 to Ordered,
> etc.
>
> I could create a new column, copy all the data over, then delete the
> old column, but I suspect there's some cool way to go about doing
> this.  Thanks!
>
> Mike
>
> PS - This is totally shameless, but if you cook or your spouse does,
> I'd totally appreciate it if you could take a short survey to help us
> develop this website we're working on.  We're hoping to get about a
> thousand responses so I have to plug it everywhere :)  This URL is
> http://survey.kitchenpc.com/
>

Re: Modifying an existing table to use an ENUM instead of an int

From

Mike Christensen

Date:

07 June 2010, 04:49:22

Doh!  I suck, I had a CHECK constraint on that column to make sure the
value was between 0 and 4 (the whole reason I was switching to an
enum).  Once I dropped the constraint, the ALTER TABLE worked
perfectly.

I guess I have to gripe about the error message though, it should tell
me there was a problem enforcing an existing constraint on the column.

Mike

On Mon, Jun 7, 2010 at 12:40 AM, Mike Christensen <mike@kitchenpc.com> wrote:
> Ok, I'm convinced this should work but it does not:
>
> CREATE FUNCTION ConvertIntToOrderStateEnum(state integer) RETURNS
> OrderStateEnum AS
> $BODY$
> SELECT CASE WHEN $1=0 then 'Preview'::OrderStateEnum
>   WHEN $1=1 then 'InQueue'::OrderStateEnum
>   WHEN $1=2 then 'Ordered'::OrderStateEnum
>   WHEN $1=3 then 'Error'::OrderStateEnum
>   WHEN $1=4 then 'Cancelled'::OrderStateEnum ELSE NULL END;
> $BODY$
> LANGUAGE 'sql' IMMUTABLE STRICT;
>
> ALTER TABLE orders ALTER orderstate TYPE OrderStateEnum
> USING ConvertIntToOrderStateEnum(OrderState);
>
> I get the error:
>
> ERROR: operator does not exist: orderstateenum >= integer
> SQL state: 42883
> Hint: No operator matches the given name and argument type(s). You
> might need to add explicit type casts.
>
> On Mon, Jun 7, 2010 at 12:11 AM, Mike Christensen <mike@kitchenpc.com> wrote:
>> Hi all -
>>
>> I have an existing table that looks like this:
>>
>> CREATE TABLE orders
>> (
>>  --Bunch of stuff you don't care about
>>  orderstate integer NOT NULL,
>>  --Etc
>> )
>>
>> with a bunch of data in it.  I've now created this new data type:
>>
>> CREATE TYPE OrderStateEnum AS ENUM ('Preview', 'InQueue', 'Ordered',
>> 'Error', 'Cancelled');
>>
>> I want to change the type of "orderstate" from integer to
>> OrderStateEnum, and cast 0 to Preview, 1 to InQueue, 2 to Ordered,
>> etc.
>>
>> I could create a new column, copy all the data over, then delete the
>> old column, but I suspect there's some cool way to go about doing
>> this.  Thanks!
>>
>> Mike
>>
>> PS - This is totally shameless, but if you cook or your spouse does,
>> I'd totally appreciate it if you could take a short survey to help us
>> develop this website we're working on.  We're hoping to get about a
>> thousand responses so I have to plug it everywhere :)  This URL is
>> http://survey.kitchenpc.com/
>>
>