Postgres batch updating

Inspired by https://minhajuddin.com/2020/10/17/how-to-do-batch-updates-in-postgresql/

I needed a way to batch update a table in Postgres without locking it or having it timeout. Tried a few ways of doing it but this turned out to be the most reliable.

CREATE TEMP TABLE things_to_be_updated AS
  SELECT ROW_NUMBER() OVER(ORDER BY id) row_id, id FROM things;

CREATE INDEX ON things_to_be_updated(row_id);

UPDATE things t
SET col_to_update = updated
FROM things_to_be_updated bu
WHERE bu.id = t.id
AND bu.row_id > 2000 AND bu.row_id  <= 3000;

DROP TABLE things_to_be_updated;

You have to run it multiple times for each batch but it meant we could control how many we updated each time. In the article the author suggests using a script to generate the require SQL which is a nice idea and saves adjusting the row_id manually.