Skip to content

Instantly share code, notes, and snippets.

@kanmaniselvan
Created September 3, 2020 14:46
Show Gist options
  • Save kanmaniselvan/63b190069d2ce37d42cc8c1b8f3a0adc to your computer and use it in GitHub Desktop.
Save kanmaniselvan/63b190069d2ce37d42cc8c1b8f3a0adc to your computer and use it in GitHub Desktop.
Updates random array in batches of 1000 records
do $$
declare
row_count integer := 449166;
batch_size integer := 1000;
offset_value integer := 0;
begin
while row_count > 0 loop
update
my_table
set array_column = ('{' || regexp_replace(repeat((array['X,', 'Y,X,', 'T,X,C,', 'T,Y,', 'Z,'])[floor(random() * 4 + 1)], floor(random() * 3 + 1)::integer), '.$', '') || '}')::varchar[]
where id in (select id from my_table offset offset_value limit batch_size);
raise notice 'Row remaining %', row_count ;
row_count := row_count - batch_size;
offset_value := offset_value + batch_size;
end loop;
end; $$
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment