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