In UPDATE ... RETURNING, are the returned values the pre-update or post-update ones?

## The default is the after-image A `RETURNING` clause on an `UPDATE` is evaluated against the row as it exists once the statement has written it. Given a balance of 100: ```sql UPDATE accounts SET balance = balance - 10 WHERE account_id = 1 RETURNING account_id, balance; -- balance comes back as 90 ``` The arithmetic on the right-hand side of `SET` reads the old value, but the output list reads the stored result. Anything else the write produced is visible too: a `last_modified` column driven by a default or a trigger, a generated column recomputed from the new inputs, a value a before-update trigger overrode. ## Why that default is the useful one The caller already knows what it asked for; what it does not know is what the server actually did. Increments computed in SQL, clamping done by a trigger, values derived by a generated column — all of these mean the stored row can differ from the intent. Returning the after-image lets the application refresh its in-memory copy from the authoritative version without a second read. ## DELETE and INSERT The rule generalises as "return the row version this statement leaves behind, and if there is none, the version it removed": - `DELETE ... RETURNING *` returns the rows exactly as they were before removal. There is no post-image, and this is your last opportunity to see the data — a natural fit for archiving or logging what was purged. - `INSERT ... RETURNING` has no prior version, so the returned values are simply the newly stored row. ## Getting the before-image Plain `RETURNING` does not give you the old values on an `UPDATE`. Engines in the `OUTPUT` family do, because the clause is defined over two pseudo-tables representing the row versions: ```sql -- T-SQL UPDATE accounts SET balance = balance - 10 OUTPUT deleted.balance AS balance_before, inserted.balance AS balance_after WHERE account_id = 1; ``` `deleted` holds the pre-modification image and `inserted` the post-modification image. On an `INSERT` the `deleted` columns are NULL (nothing preceded the row); on a `DELETE` the `inserted` columns are NULL. That symmetry is what makes `OUTPUT` a natural audit-trail feed: one statement can write the change and record both sides of it. With a `RETURNING`-only engine you have three practical options. Read the row before the update inside the same transaction and keep the value in the application. Compute the old value from the new one when the change is invertible (`balance + 10`). Or have a trigger record the prior image into a history table as part of the write. Some engine releases add explicit row qualifiers to `RETURNING` so both images can be projected; check your engine's documentation rather than assuming, because this is exactly the kind of detail that varies. ## Triggers, defaults and generated columns Because the output list sees the final stored row, `RETURNING` is the cleanest way to observe server-side derivation. If a before-update trigger rounds a price, `RETURNING price` shows the rounded value. If `updated_at` is maintained by a default or trigger, `RETURNING updated_at` shows the new timestamp. This is a genuine advantage over echoing the parameters you sent, which is what naive code does and which silently diverges the moment any server-side rule applies. ## What interviewers are probing Two things. First, whether you know which image you are looking at — a candidate who says `RETURNING balance` yields 100 has an incorrect mental model of when the clause is evaluated. Second, whether you know that the before-image is not available for free: the interviewer is usually heading towards an audit or change-feed requirement, and wants to see whether you reach for the `OUTPUT`-style two-image form, a trigger, or an explicit read inside the transaction — rather than assuming `RETURNING` will hand you both.