Command Help : Alter table modify column

Database
Enthusiast

Command Help : Alter table modify column

Hi All,

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?

Tags (4)
11 REPLIES
Enthusiast

Re: Command Help : Alter table modify column

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!

Enthusiast

Re: Command Help : Alter table modify column

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.

Enthusiast

Re: Command Help : Alter table modify column

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.

Junior Contributor

Re: Command Help : Alter table modify column

@KS42982:

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;

Dieter

Enthusiast

Re: Command Help : Alter table modify column

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.

Junior Contributor

Re: Command Help : Alter table modify column

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.

Dieter

Enthusiast

Re: Command Help : Alter table modify column

Hi All,

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 .. :(

Teradata Employee

Re: Command Help : Alter table modify column

Pretty Simple.

ADD command would be used to alter the datatype of the existing field.

ALTER TABLE <DatabaseName>.<TableName>

ADD <ExistingColumnName> <New Data Type>

;

Br,

Asad

Fan

Re: Command Help : Alter table modify column

how can i change  the data in the existing table