Changing column datatype from DECIMAL(9,0) to DECIMAL(15,0)

Database
Enthusiast

Changing column datatype from DECIMAL(9,0) to DECIMAL(15,0)

Can you please help me concerning this matter (I didn´t found it in the Teradata documentation, which is honestly little overwhelming): My table has this column -BAN DECIMAL(9,0)-, and now I want to change it to - BAN DECIMAL(15,0) COMPRESS 0.- How can I do it?

What does COMPRESS constraint 0. or any other mean anyway?

I hope BAN DECIMAL(9,0) to - BAN DECIMAL(15,0)  is possible, and I don`t have to create a new table and then copy the data form the old table. The table is very very big - when I do COUNT(*) form that table I get this error: 2616 numeric overflow occurred during computation

2 REPLIES
Teradata Employee

Re: Changing column datatype from DECIMAL(9,0) to DECIMAL(15,0)

You have to create a new table and copy the data for this change. Some types of changes can be done with ALTER TABLE, but not DECIMAL(9) to DECIMAL(15).

COMPRESS 0 means that if the value in the column is zero, a flag will be set in the rowheader and the 8 byte field that would otherwise hold the value will be omitted - so the storage format of the row on disk will be shorter. The number of "presence bits" taken up in every rowheader depends on how many values you choose to compress - one bit for one value, two bits for 2 or 3 values, three bits for 4 to 7 values, etc. up to 255 values in 8 bits.

BTW - you can CAST(COUNT(*) AS BIGINT) or as DECIMAL(15) or (18) to avoid the numeric overflow.

Supporter

Re: Changing column datatype from DECIMAL(9,0) to DECIMAL(15,0)

If the column is not part of an index you can also try the following approach of adding a new column, update the new column and drop  / rename columns

create table a_a (a integer, b decimal(8,0));

alter table a_a add b_new decimal(15,0) compress(0);
update a_a set b_new = b;

alter table a_a
drop b,
rename b_new to b;

show table a_a;

drop table a_a;