From: | "Igal (at) Lucee(dot)org" <igal(at)lucee(dot)org> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | DDL and DML in a transaction |
Date: | 2019-07-31 18:38:39 |
Message-ID: | 28ca745e-f459-95bd-bf80-7d907b65b9cf@lucee.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2019-07-31 18:52:34 | Re: DDL and DML in a transaction |
Previous Message | Tom Lane | 2019-07-31 16:20:04 | Re: Which version to upgrade upto |