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 <mle...@entrata.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 <xly...@gmail.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 <mle...@entrata.com> 于2020年12月4日周五 上午1:11写道： >> >>> On Wed, Dec 2, 2020 at 11:53 PM charles meng <xly...@gmail.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. >>> >>
