I would like to run an update as below on a 100 million row table in batches so as to not cause locking/without downtime and not affecting replication.
Update tbl
set col_new = col
where (do 1 day at a time)
perform pg_sleep(120);
col is timestamp without time_zone, col_new is timestamptz
Can someone help me with the syntax?
do 1 day at a timepart, you'll need to provide more information about your table. If the problem is withpg_sleep, see the PostgreSQL docs. If it's how to iterate through the dates, check the docs onWHILE.CREATE TABLEstatements for the tables in question adding the desired output. Those answering need a Minimal, Complete, and Verifiable example. Use formatted text please, no screen shots.