Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a Postgres 8.4 table with columns that are of type "character varying", but I want to convert them to an enum type.

When trying to alter the table

ALTER TABLE logs ALTER COLUMN interface_type TYPE interface_types
USING interface_type::text::interface_types;

I get an error

default for column "interface_type" cannot be cast to type interface_types

The default value for the column IS a text version of a value in the enum types. So it should be possible, though I've had no luck googling for the solution.

I assume that I need to define a cast for it. How?

share|improve this question
add comment

1 Answer

I think I've found the answer: I need to drop the default first, and then re-add it.

ALTER TABLE logs ALTER COLUMN interface_type DROP DEFAULT;
ALTER TABLE logs ALTER COLUMN interface_type TYPE interface_types
  USING interface_type::text::interface_types;
ALTER TABLE logs ALTER COLUMN interface_type SET DEFAULT 'button';
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.