Skip navigation
Up to Discussions in Enzee Universe
18335 Views 3 Replies Latest reply: Mar 8, 2010 10:50 AM by Okhan RSS
gdbdba New Enzee
gdbdba
2 posts since
Nov 11, 2009
Currently Being Moderated

Nov 16, 2009 7:01 PM

Changing the Datatype of a Column

We are in the process of creating a new DWH and right now Table structure and attributes change based on the recommendations from the Business analysts. I came accross a situation where in i have to change the datatype of a column

 

Is it possible to change the datatype of a particular column in a table?

As an example, In my case i am trying to change the datatype to VARCHAR(10) from NUMERIC(18,0) and vice-versa. I know we can change the lengtht for VARCHAR datatype but not sure if a change of datatype is possible?

 

 

I Tried

ALTER TABLE ACCOUNT_DIM_P12 MODIFY COLUMN (CPY_NO VARCHAR(10));

 

and got the following error msg:

 

ERROR [HY000] ERROR:  ALTER TABLE: only varchar fields can be modified
0 records returned.
ALTER TABLE Command Failed.

Tags: table, alter, column, datatype
  • David Birmingham Active Enzee
    David Birmingham
    426 posts since
    Sep 24, 2007
    Currently Being Moderated
    1. Nov 16, 2009 7:51 PM (in response to gdbdba)
    Re: Changing the Datatype of a Column

    No can do. The only way to change the data type is to manufacture a table containing the new type, and copy the data from the source table into the new one

     

    This is usually not an issue - most envirionments schedule this activity associated with a data backup or other maintenance activity. it can be bothersome at times

    durinig initial development, but your processing model should stabilize rapidly. Some folks add intermediate 'shadow' tables with the additional columns in them,

    using a unique surrogate to match them to their parent in the original - and simply link them through a view. The two tables are reconciled later on a maintenance

    boundary - lots of ways to skin a cat - it just depends on what your skinning time window is.

  • gdbdba New Enzee
    gdbdba
    2 posts since
    Nov 11, 2009
    Currently Being Moderated
    2. Nov 16, 2009 7:59 PM (in response to David Birmingham)
    Re: Changing the Datatype of a Column

    Thanks !!!!!!!!!!

    So i am looking at creating a new_table with the new datatype for the column and then populating it with the data from the source_table, dropping the old_table and renaming the new_talble to source_table. If thats correct what would be the best way load new_table with data from source_table.

  • Okhan New Enzee
    Okhan
    7 posts since
    Feb 18, 2010
    Currently Being Moderated
    3. Mar 8, 2010 10:50 AM (in response to gdbdba)
    Re: Changing the Datatype of a Column

    Since you are just changing the datatype but not the table def you can use 'Insert into new_table name select * from old_table name;'  This will populate the data from old table into the new table.

More Like This

  • Retrieving data ...

Bookmarked By (0)