All Forums Database
Dantes 9 posts Joined 06/12
10 Aug 2012
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

Fred 1096 posts Joined 08/04
11 Aug 2012

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.

ulrich 816 posts Joined 09/09
12 Aug 2012

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;

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.