Skip to content

Instantly share code, notes, and snippets.

@ahobson
Created September 18, 2023 14:49
Show Gist options
  • Save ahobson/451f1a5c34a5b96c8c8e8958550e4bad to your computer and use it in GitHub Desktop.
Save ahobson/451f1a5c34a5b96c8c8e8958550e4bad to your computer and use it in GitHub Desktop.
code golf for batch update in postgresql
-- this is a naive way that seems plenty fast
DO $$
DECLARE
rec record;
BEGIN
RAISE INFO 'Starting %', now();
FOR rec IN SELECT id
FROM addresses
WHERE country = 'United States'
LOOP
UPDATE addresses SET country = 'US' WHERE id = rec.id;
END LOOP;
RAISE INFO 'Finishing %', now();
END $$;
@ahobson
Copy link
Author

ahobson commented Sep 18, 2023

Trying to batch with a cursor doesn't seem any faster

DO $$
DECLARE
	_cur		CURSOR FOR
				SELECT id
				FROM addresses
				WHERE country = 'United States';
	ids         uuid[];
	rec			record;
BEGIN
	OPEN _cur;
	RAISE INFO 'Starting at %', clock_timestamp();
	LOOP
		ids := array[]::uuid[];
		FOR rec IN FETCH 10 FROM _cur
		LOOP
			SELECT array_append(ids, rec.id) INTO ids;
 		END LOOP;
		EXIT WHEN CARDINALITY(ids) = 0;
		UPDATE addresses SET country = 'US' WHERE id = ANY(ids);
	END LOOP;
	CLOSE _cur;
	RAISE INFO 'Done at %', clock_timestamp();
END $$;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment