From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Batch process |
Date: | 2022-07-20 18:56:41 |
Message-ID: | 068d478e-1302-bf5c-9481-3b2c532f8ffc@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
How will DELETE WHERE sales_id IN (...); , given that test_old has no index?
Of course, we don't know if there's an index on sales.bill_date, since OP's
only response has been another "tell me how to do it".
On 7/20/22 13:32, Adrian Klaver wrote:
> 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.
>>>
>>>
>>
>
>
--
Angular momentum makes the world go 'round.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2022-07-20 19:03:34 | Re: Batch process |
Previous Message | Tom Lane | 2022-07-20 18:39:39 | Re: plan for function returning table combined with condition |