On Thu, Dec 3, 2020 at 10:18 AM Rich Shepard <rshep...@appl-ecosys.com> wrote:
> On Thu, 3 Dec 2020, Michael Lewis wrote: > > > On Wed, Dec 2, 2020 at 11:53 PM charles meng <xly...@gmail.com> wrote: > > >> 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? > > > 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. > > Tell me, please, why > > ALTER TABLE <tablename> ALTER COLUMN <columnname> SET DATA TYPE BIGINT > > will not do the job? > > I've found some varchar columns in a couple of tables too small and used > the > above to increase their size. Worked perfectly. > > Regards, > > Rich > Afaik, it will require an access exclusive lock for the entire time it takes to re-write the 1.6 billion rows and update all indexes. That sort of lock out time doesn't seem workable in many production systems.
