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?
Thank you,
Herouth