Re: An archiving query - is it safe?

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 will
bemore efficient.
 
>
> Is this safe to do? Is there any danger of losing data? Is it atomic?

Yes.  No.  Yes.

-- 
Vik