Modifying an existing table to use an ENUM instead of an int
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/
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.
Show quoted text
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/
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
Show quoted text
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/