Thread: DDL and DML in a transaction

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

Thank you, David.

I should get more sleep...

Igal

On 7/31/2019 11:52 AM, David G. Johnston wrote:

On Wed, Jul 31, 2019 at 11:38 AM Igal @ Lucee.org <igal@lucee.org> wrote:

     alter table some_table
         rename column amount_num to amount_text;

     alter table some_table
         drop column amount_num;

You just renamed amount_num to amount_text so I'm not sure why you expect the drop to succeed.

Would it have less locking than simply altering the column?

I doubt anything will improve upon simply altering the column.  You have to perform a full table rewrite in either case which is going to be the main resource consumer.

David J.