Results 1 to 3 of 3

Thread: Altering a TYPE

  1. #1
    Join Date
    Mar 2007
    Posts
    25

    Unanswered: Altering a TYPE

    I need to change the deffimition of an enumerated type and have not found anything in the 8.3 documentation assuming the following I have created a type:
    CREATE TYPE color_chart AS ENUM('Blue', 'Red', 'Green');

    I now need to add Purple to the list. The only way so far I have found to do this is as follows:
    1.- Backup the table
    2.- Drop the table
    3.- Drop the TYPE
    4.- Re-create the type
    5.- Re-create the table
    6.- Restore the data

    Is this the only way to accomplish this? Is there a simple way to do the same thing?

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    It depends. If you are cool enough (can edit system catalogs) then there is an easier way.

    Code:
    INSERT INTO pg_enum(enumtypeid, enumlabel)
    SELECT oid, 'Purple'
    FROM pg_type 
    WHERE typtype='e' AND typname='color_chart'

  3. #3
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by obwan
    Is this the only way to accomplish this?
    Yes as far as I know

    Search the mailing list archives for more information I think this has been discussed several times:
    http://www.nabble.com/PostgreSQL---general-f768.html


    Is there a simple way to do the same thing?
    Yes, use a properly normalized model and put the colors into their own table.
    Enums are evil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •