DDL and DML in a transaction

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

Responses

Browse pgsql-general by date

  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