Re: Issue with delete

On Thu, Jun 24, 2021 at 5:08 AM Rohit Rajput <rht(dot)rajput(at)yahoo(dot)com> wrote:

> Good day everyone,
>
> I have few tables where i need to delete above 50 million rows each. Since
> i dont want to delete them all in one shot and block my table for extended
> time, i am trying to use limit with delete but its giving me error every
> time. May i please get some assistance here? Thanks
>
> Here is what i am running:
>
> delete from mytable where id in (select id from mytable where
> condition=true limit 1000);
>
> where i run this, i get following error:
>
> Error: current transaction is aborted, commands ignored until end of
> transaction block.
> SQL state: 25P02.
>
>
> I am running it in RDS.
>
>
> TIA, Best,
> Rohit
>

As others said, there is no limit with the UPDATE/DELETE statements in
PostgreSQL. However, you can get around that using a common table
expression (CTE). We have a blog post on doing exactly this up at
CrunchyData

https://blog.crunchydata.com/blog/simulating-update-or-delete-with-limit-in-postgres-ctes-to-the-rescue

Keith