WITH moved AS ( DELETE FROM src WHERE .. RETURNING * ) INSERT INTO dst SELECT * FROM moved;
I know that's effectively an atomic action, but I'd feel a lot more comfortable reversing that logic so the delete is based on the results of the insert.
WITH saved AS ( INSERT INTO dst SELECT * FROM src WHERE ... RETURNING * ) DELETE FROM src WHERE ...;
I'll admit yours is cleaner, though. :)
That is a good idea. I didn't even realize that there was such a command, so I'll definitely use those.