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

Yes. No. Yes.

--
Vik