Yohanes Iwan Sugiart wrote:
I want to change a column's data type from varchar(32) to varchar(30). -> or anything shorter. As we all know, Firebird prevents this, I've also read somewhere that the only way is by creating new column with desired data type/column width and copy data manually. Is it true? Is there any easier way to perform this task?
Helen Borrie answers:
Yes, you have to do it manually, but it is pretty simple. When copying the data, you will need to use substring() to ensure you don't get any overflows. Of course, any values that are currently longer than 30 characters will be truncated:
update atable
set newsurname = substring (surname from 1 for 30 )
In an isql script (exclusive access, please!):
alter table atable add newsurname varchar(30);
commit;
update atable
set newsurname = substring (surname from 1 for 30);
commit;
alter table atable drop surname;
commit;
alter table atable add surname varchar(30);
commit;
update atable set surname = newsurname;
commit;
alter table drop newsurname,
alter surname position 4; /* <--- whatever position surname had before */
commit;