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
