Re: Alter the column data type of the large data volume table.

What I mean is that it will take a lot of time to complete all data
processing.I have to say that it is a good solution to adjust the column
type without affecting online users.

I found a tool on github, see the link below, unfortunately, this is for
MySQL...

https://github.com/github/gh-ost

regards.

Michael Lewis <mlewis(at)entrata(dot)com> 于2020年12月4日周五 下午1:04写道：

> Please do reply all.
>
> Not sure what you mean about it taking too much time. It's rewriting a
> bunch of data. It's going to take a while. The best you can do is break the
> work up into small pieces and commit each piece.
>
> On Thu, Dec 3, 2020, 7:11 PM charles meng <xlyybz(at)gmail(dot)com> wrote:
>
>> Thanks for your help, I think the first method I tried (adding temporary
>> column) is similar to what you said, but it takes too much time for me.
>>
>>
>> Thanks again.
>>
>> Michael Lewis <mlewis(at)entrata(dot)com> 于2020年12月4日周五 上午1:11写道：
>>
>>> On Wed, Dec 2, 2020 at 11:53 PM charles meng <xlyybz(at)gmail(dot)com> wrote:
>>>
>>>> Hi all,
>>>>
>>>> I have a table with 1.6 billion records. The data type of the primary
>>>> key column is incorrectly used as integer. I need to replace the type of
>>>> the column with bigint. Is there any ideas for this?
>>>>
>>>> Solutions that have been tried:
>>>> Adding temporary columns was too time-consuming, so I gave up.
>>>> Using a temporary table, there is no good way to migrate the original
>>>> table data to the temporary table
>>>>
>>>> Thanks in advance.
>>>>
>>>
>>> You can add a new column with NO default value and null as default and
>>> have it be very fast. Then you can gradually update rows in batches (if on
>>> PG11+, perhaps use do script with a loop to commit after X rows) to set the
>>> new column the same as the primary key. Lastly, in a transaction, update
>>> any new rows where the bigint column is null, and change which column is
>>> the primary key & drop the old one. This should keep each transaction
>>> reasonably sized to not hold up other processes.
>>>
>>