My Postgres Cheatsheet

batch-updates.sql

DO LANGUAGE plpgsql $$
DECLARE counter INTEGER = 1;
DECLARE tick INTEGER = 1;
BEGIN
  WHILE counter > 0 LOOP
    UPDATE table
    SET field = NULL
    WHERE id IN (
      SELECT id
      FROM table
      WHERE
      field IS NOT NULL
      LIMIT 100000
    );
    GET DIAGNOSTICS counter = ROW_COUNT;
    raise notice 'Updated: %', counter;
    raise notice 'Overall: %', tick*counter;
    tick = tick + 1;
    COMMIT;
  END LOOP;
END$$;

convert-uuid-inline.sql

ALTER TABLE table ALTER COLUMN id type uuid using id::uuid;