[jpitz]

> My instinct here is to write the query in such a way that even if pg changes in the future, or if I migrate to an almost compatible alternative tomorrow, it should work the same way.

I agree, but, to nitpick, if transaction semantics change that much, you aren't dealing with an almost compatible alternative. Further, I'll wager vast sums of money that pg will not change in that way, ever.

> In the end, queries are code, and code is our way to communicate or intent to the next developer, so it's better to do the delete after the insert

I wholeheartedly agree, so long as your code correctly captures the requirements. For ( contrived, uncommon ) example if the requirement states that, transactionally, the row must either be in either the live table or the archive table, but not both ( for reporting non-duplication, ) then the form that performs the insert-delete is the correct capture of that requirement.