From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Ron <ronljohnsonjr(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Batch process |
Date: | 2022-07-20 18:32:42 |
Message-ID: | 1f6f563f-2f58-8749-5492-4bed9f67df9f@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 7/20/22 11:16 AM, Ron wrote:
> But the first thing he does is drop test_old.
Then:
Create table test_old as select * from sales where bill_date<now()
-interval '1 year';
At that point you could do either:
Delete from sales where sales_id in (select sales_id from test_old);
or
DELETE FROM sales WHERE bill_date<NOW() -INTERVAL '1 year';
>
> On 7/20/22 09:52, Adrian Klaver wrote:
>> On 7/20/22 01:28, Ron wrote:
>>> On 7/20/22 00:08, Rama Krishnan wrote:
>>>> Hi All,
>>>>
>>>> I am doing purge activity my sales table contains 5M records I am
>>>> going to delete more than 1 year data (which was 3M) records so it
>>>> was running more so I want to do batch wise deletion through plsql
>>>>
>>>>
>>>>
>>>>
>>>> created or replace function data_purge() returns void as$$
>>>> Declare
>>>> Begin
>>>> Drop table test_old;
>>>> Create table test_old as select * from sales where bill_date<now()
>>>> -interval '1 year';
>>>>
>>>> Delete table sales where sales_id in (select sales_id from test_old;
>>>>
>>>> End;
>>>> $$ language plpgsql;
>>>>
>>>>
>>>> Kindly guide me
>>>
>>> Why not just DELETE FROM sales WHERE bill_date<NOW() -INTERVAL '1
>>> year';?
>>>
>>
>> Because it looks like the OP is saving the old records in test_old.
>>
>>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2022-07-20 18:39:39 | Re: plan for function returning table combined with condition |
Previous Message | Ron | 2022-07-20 18:16:49 | Re: Batch process |