In oracle: employees table contain more than 10 million data. For update I'm using rownum
for faster update.
CREATE OR REPLACE PROCEDURE emp_up AS
begin
loop
update employees set commission_pct=0 where commission_pct is null and rownum <=10000;
exit when sql%notfound;
commit;
end loop;
end;
In postgresql: postgresql does not support rownum. Is there any way to update employees table in postgres with the similar way in oracle?
Best Answer
In PostgreSQL a function is always be executed in one transaction. It does not matter if you loop over batches of 10.000 rows or just
UPDATE
the whole table.Thanks to MVCC there should also no need to split the UPDATE in order to allow other processes to do something on the table, as the locks are very lightweight in this scenario.
So, the correct replacement would be: