I am trying to change a text column into a numeric one in a large table.
My idea was to add a new column, update it, drop the old column, and
rename the new one to the old name. I am hoping that that would make it
faster and minimize locking time though I'm not sure that it would.
I am therefore trying to execute the following but I'm getting an error
that the new column does not exist:
begin;
alter table some_table
add column if not exists amount_num numeric(30,12);
update some_table
set amount_num = amount_text::numeric(30,12);
alter table some_table
drop column amount_text;
alter table some_table
rename column amount_num to amount_text;
alter table some_table
drop column amount_num;
commit;
end;
Am I missing something? Is this supposed to work?
Would it have less locking than simply altering the column?
Thanks,
Igal
