Is it possible to do an ALTER TABLE from a VARCHAR into TEXT fiel

We want to convert one of the columns in a table. Now it's a VARCHAR(150) field and we want to translate it into a TEXT field. I've tried to do an alter table, but that doesn't work. I can do an unload, drop table, create table (text) ,do a reload and recreate the indexes. The table has 35.000.000 records. Does anyone know a shorter way to do that ? Thanks, Franky Thiel Infohos Tel : 050.45.99.93 Fax : 050.45.99.69 mailto:franky.thiel@infohos.be

Thiel Franky <franky.thiel@infohos.be> writes: > We want to convert one of the columns in a table. > Now it's a VARCHAR(150) field and we want to translate it into a TEXT field. > I've tried to do an alter table, but that doesn't work. > > I can do an unload, drop table, create table (text) ,do a reload and > recreate the indexes. > The table has 35.000.000 records. > > Does anyone know a shorter way to do that ? I'm glad you said shorter (and not faster): add text-field update textfield (= varchar-field) delete varchar-field create new indexes on text-field Thomas

Here's another method. Depending on how many other columns the table has, this may or may not be faster. 1. Unload the primary key columns & the varchar column. 2. Create a table, stg_table, with the primary key columns and a text column. 3. Load result of 1 into this table. Create an index (not necessarily unique) on the primary key columns. 4. Alter original table, adding a text column, preferably into a blobspace (so that the update is not logged). 5. Update information from stg_table into orig_table as follows update orig_table set new_text_column = ( select text_col from stg_table where stg_table.pk_col1 = orig_table.pk_col1 and stg_table.pk_col2 = orig_table.pk_col2 and ...); 6. Check. Drop original varchar column. Rename new Text column. Needless to say, test the process on a small subset of data first. Rudy Thiel Franky wrote: > We want to convert one of the columns in a table. > Now it's a VARCHAR(150) field and we want to translate it into a TEXT field. > I've tried to do an alter table, but that doesn't work. > > I can do an unload, drop table, create table (text) ,do a reload and > recreate the indexes. > The table has 35.000.000 records. > > Does anyone know a shorter way to do that ? > > Thanks, > > Franky Thiel > Infohos > > Tel : 050.45.99.93 > Fax : 050.45.99.69 > mailto:franky.thiel@infohos.be