Re: An archiving query - is it safe?

On 14/01/2014, at 14:55, Vik Fearing wrote:

> On 01/14/2014 12:06 PM, Herouth Maoz wrote:
>> I have regular archiving scripts which traditionally did something like this
>>
>> BEGIN TRANSACTION;
>>  INSERT INTO a__archive
>>  SELECT * FROM a
>>  WHERE <condition>; -- date range condition
>>
>>  DELETE FROM a
>>  WHERE <condition>; -- same date range condition
>> COMMIT;
>>
>> This is "classic" SQL. I'm thinking of changing this into something like:
>>
>> WITH del AS ( DELETE FROM a WHERE <condition> RETURNING * )
>> INSERT INTO a__archive SELECT * FROM del;
>>
>> As this would only access table "a" once, deleting and returning the records in the same access, which I believe
willbe more efficient. 
>>
>> Is this safe to do? Is there any danger of losing data? Is it atomic?
>
> Yes.  No.  Yes.

Thank you, I will proceed with this plan, then.


Herouth