<div>
Might we ask why you want to do it in batches?<br />
<br />
MOST of the time, this isn't really faster and can consume more resources.<br />
<br />
There is also to issue of committing. &nbsp;If you commit per batch, what if one fails? &nbsp;Then you can have data that isn't as complete as you desire.
</div>


Might we ask why you want to do it in batches?

MOST of the time, this isn't really faster and can consume more resources.

There is also to issue of committing.  If you commit per batch, what if one fails?  Then you can have data that isn't as complete as you desire.

<div>
Thank you! yes, I understand the negative impacts. i woild like to see how it can be accomplished, if there's a way.
</div>


Thank you! yes, I understand the negative impacts. i woild like to see how it can be accomplished, if there's a way.

pvsbandi

<div>
<p>I know the CTE way of doing it. But I was manually triggering it till the end of the record.</p><p>&nbsp;How to automate the repetition in a loop?</p>
</div>


I know the CTE way of doing it. But I was manually triggering it till the end of the record.

 How to automate the repetition in a loop?

<div>
&gt;&gt;How to automate the repetition in a loop?<br />
<br />
Use plpgsql: &nbsp;Create a procedure or anonymous code block:<br />
<a href="https://www.postgresql.org/docs/current/plpgsql-control-structures.html" rel="ugc">https://www.postgresql.org/docs/current/plpgsql-control-structures.html</a>
</div>


>>How to automate the repetition in a loop?

Use plpgsql:  Create a procedure or anonymous code block:
https://www.postgresql.org/docs/current/plpgsql-control-structures.html [https://www.postgresql.org/docs/current/plpgsql-control-structures.html]

<div>
To get the number of rows affected by the update or delete, look at the returning clause:<br />
<a href="https://www.postgresql.org/docs/current/plpgsql-statements.html" rel="ugc">https://www.postgresql.org/docs/current/plpgsql-statements.html</a><br />
<br />
You can see it in action here:<br />
<a href="https://stackoverflow.com/questions/4038616/get-count-of-records-affected-by-insert-or-update-in-postgresql" rel="ugc">https://stackoverflow.com/questions/4038616/get-count-of-records-affected-by-insert-or-update-in-postgresql</a><br />

</div>


To get the number of rows affected by the update or delete, look at the returning clause:
https://www.postgresql.org/docs/current/plpgsql-statements.html [https://www.postgresql.org/docs/current/plpgsql-statements.html]

You can see it in action here:
https://stackoverflow.com/questions/4038616/get-count-of-records-affected-by-insert-or-update-in-postgresql [https://stackoverflow.com/questions/4038616/get-count-of-records-affected-by-insert-or-update-in-postgresql]


<div>
<div class="content wysiwyg-content fr-view">
<p>I want to know if there's a way in Postgres Plpgsql, to perform an update or a delete in batches (say, a batch of 1000) till the end of the last record.</p><p>Could someone please share an example, if a better solution is feasible?</p>
</div>
</div>



I want to know if there's a way in Postgres Plpgsql, to perform an update or a delete in batches (say, a batch of 1000) till the end of the last record.
Could someone please share an example, if a better solution is feasible?

Postgres - DML in batches

PostgreSQL is an object-relational database management system with an emphasis on extensibility and standards-compliance. As a database server, its primary function is to store data securely, supporting best practices, and to allow for retrieval at the request of other software applications. PostgreSQL implements the majority of the SQL2011 standard, is ACID-compliant and transactional using multiversion concurrency control (MVCC), and handles complex SQL queries using many indexing methods that are not available in other databases. It is cross-platform and runs on many operating systems including Linux, FreeBSD, OS X, Solaris, and Microsoft Windows.