I have an existing table in TERADATA and it has a field named log_dttm defined as timestamp.
I want to change the column data type to varchar(30).
"alter table modify" failed to convert.
ALTER TABLE Failed. 3558: Cannot alter the specified attribute(s) for log_dttm.
Is there any direct command to accomplish this?
ALTER TABLE to change the datatype of existing columns is very limited. You can use this statement to change the VARCHAR and increase the number of characters but I don't think you can do that to change TIMESTAMP to VARCHAR.
You can add a column and insert the data values in it while casting to VARHCAR and later on rename the column if required!
Thanks for your inputs ...
I am aware of this approach.
However, wanted some inputs as there are 25 columns to be renamed !!!
Further, i need to maintain the same order of columns as there is load process enabled to load a "|" separated file.
As you need to rename so many columns, I'd suggest, you create a new table with desired datatypes, copy all the data from the existing table to this new table by casting to varchar, drop the existing table and re-create with new datatypes and insert the records from the temp table to this newly create table and finally drop the temp table. It would be a much cleaner and safer approach for such kind of operations.
Why use a temp table and drop it
Create a new table with the required definition, insert/select and the rename the tables:
create newtab (...);
ins newtab sel * from oldtab;
collect stats on newtab from oldtab; -- only for those columns where the datatype didn't change
drop table oldtab;
rename newtab to oldtab;
That is definately a good approcah. However, what I hear from DBAs in my company that RENAMEing table is not a good stable thing to do in teradata. So in such kind of situations, they always prefer us to do using temp table.
I never heard of problems regarding RENAME.
It just needs a short exclusive lock, but you probably don't want to do that during production hours anyway.
Here DBA team has a different standardised approach as per them.
They ask you to create new columns, copy data , drop old column , rename new columns.
Its a burden on developer as to change datatype of one column, i have to play with all columns in order to maintain the same order; else from load process will fail .. :(
ADD command would be used to alter the datatype of the existing field.
ALTER TABLE <DatabaseName>.<TableName>
ADD <ExistingColumnName> <New Data Type>