how can i change a column datatype in a table (postgresql version 7.4)

[snvkgraam]

Hi,
Please let me know how can i change/modify/alter a column data type in a table.

I have a column in a table with name "Phone numeric(15,0)".
I have tried using alter command, but i did not understand how to change datatype of a column.


Thanks if anyone can solve my problem.


Thanks
Ram

 

[ceco]

the only way is create new column

(this is an example and may have some syntax errors)

alter table xxxx add column new_column number(16, 0);
update xxxx set new_column = old_column;
-- if needed now can enable NOT NULL contstraints
alter table xxxx drop column old_column;
alter table xxxx rename column new_column to old_column;

there will be problems if foreign keys or triggers depend ot this column, you should recreate them after that

well you should do this operation carefully ;-) and maybe test it somewhere else on non important data and you better do it in transaction