How can DELETE ... RETURNING move rows into an archive table in one statement?

## The problem with SELECT-then-DELETE The naive way to move rows out of a hot table is three statements: `SELECT` the rows that qualify, `INSERT` them into the archive, then `DELETE` them from the source. That has two weaknesses. It costs three round trips and materialises the rows in the client for no reason. And it repeats the predicate, so the `DELETE` re-evaluates `occurred_at < …` independently of the `SELECT` — meaning the set of rows deleted is not necessarily the set of rows archived if concurrent writes shift the boundary. Fixing that properly means capturing keys and deleting by key, which is more code again. ## The one-statement form `DELETE ... RETURNING` gives the statement itself a result set of exactly the rows it removed. In an engine that permits a data-modifying statement inside a `WITH` clause, that result set becomes a row source for a second statement: ```sql WITH archived AS ( DELETE FROM events WHERE occurred_at < DATE '2024-01-01' RETURNING * ) INSERT INTO events_archive (event_id, occurred_at, payload) SELECT event_id, occurred_at, payload FROM archived; ``` The `DELETE` runs, hands its removed rows to the CTE, and the `INSERT` consumes them. The rows inserted are, by construction, precisely the rows deleted — the predicate is evaluated once. Listing the columns explicitly on both sides is worth the extra typing: `INSERT INTO archive SELECT *` breaks silently the day someone adds a column to one table and not the other. ## The OUTPUT spelling SQL Server expresses the same idea with `OUTPUT ... INTO`, which routes the clause's output directly into a target table rather than to the client: ```sql DELETE FROM events OUTPUT deleted.event_id, deleted.occurred_at, deleted.payload INTO events_archive (event_id, occurred_at, payload) WHERE occurred_at < '2024-01-01'; ``` Note the clause order: `OUTPUT` sits between the target and the `WHERE`. The `deleted` pseudo-table holds the pre-delete image, which is the only image a delete has. Without `INTO`, the same rows stream back to the client instead. ## What the pattern buys Three things. One statement instead of three, so fewer round trips on what is often a large maintenance job. One evaluation of the predicate, so archive and source cannot disagree about which rows moved. And a single unit of work: because it is one statement, a failure anywhere in it leaves neither the delete nor the archive insert half-applied. The same shape solves related problems. `UPDATE ... RETURNING` feeding an `INSERT` writes an audit row for every row it changed. `INSERT ... RETURNING` feeding another `INSERT` populates a child table with the parent keys the server just generated — the classic "insert the order, then insert its lines against the new order id" without a round trip in between. ## Caveats worth stating Portability is the first. Allowing DML inside `WITH` is a PostgreSQL capability, not something to assume; several engines accept `RETURNING` on a standalone statement but not inside a CTE. Where the feature is missing, `OUTPUT ... INTO` or a per-dialect rewrite is the answer. Second, shape: the returned column list and the archive table's column list must correspond in count, order and type. Use explicit lists. Third, the returned rows are the pre-delete image and nothing else — you cannot enrich them with columns the deleted table never had, though the outer `SELECT` over the CTE can add literals and expressions (`SELECT event_id, occurred_at, payload, CURRENT_TIMESTAMP FROM archived`), which is the usual way to stamp an archive timestamp. Fourth, ordering inside the CTE is undefined, as with any `RETURNING` output; if the archive insert cares about order, sort in the outer `SELECT`. Finally, a very large one-shot move is still a very large statement. Deciding how to size such a job is a separate concern from the syntax; the pattern here is about how to express the move correctly, not about how much to move at once.