For a multi-row UPDATE ... RETURNING, which rows come back and in what order?

## One row per modified row The cardinality rule is simple and worth stating explicitly: a `RETURNING` clause produces exactly one output row for every row the statement modified. For ```sql UPDATE customers SET tier = 'gold' WHERE annual_spend > 1000 RETURNING customer_id, tier, annual_spend; ``` if 37 customers qualify, you get a 37-row result set. The same holds for `DELETE ... RETURNING` (one row per deleted row) and for `INSERT ... SELECT ... RETURNING` (one row per inserted row). The number of rows in the result set is therefore the affected-row count, which is why you rarely need both. ## Zero rows is information, not an error If the `WHERE` clause matches nothing, the statement succeeds and returns an empty result set. Nothing is raised. Application code can use that directly: read the returned rows, and if there are none, the row you meant to update did not exist or no longer satisfied the predicate. Without `RETURNING` you would either inspect the driver's affected-row count or issue a second `SELECT` — and the second `SELECT` answers a slightly different question, because it runs at a different point in time. This makes the clause a natural fit for conditional-update flows: attempt the update with a narrow predicate, and let the presence or absence of returned rows tell you whether your attempt took effect. ## Order is not defined There is no ordering guarantee whatsoever. The engine returns rows in whatever order it happened to touch them — which may follow an index it chose, the physical order it scanned, or the order of parallel workers. That order can change between runs and between plans, and it is emphatically not the order of the `VALUES` list or of the target table's key. The clause also has no syntax of its own for ordering or limiting: you cannot write `RETURNING ... ORDER BY ...` or attach a row limit to it. Those clauses belong to a query, and the write statement is not a query. ## Sorting or limiting the output The fix is to put a real query on top of the statement. In engines that allow a data-modifying statement inside a `WITH` clause, the idiom is: ```sql WITH updated AS ( UPDATE customers SET tier = 'gold' WHERE annual_spend > 1000 RETURNING customer_id, tier, annual_spend ) SELECT * FROM updated ORDER BY annual_spend DESC; ``` The CTE performs the update and hands its returned rows to the outer `SELECT`, which is an ordinary query and can sort, filter, aggregate or join them. If your engine does not allow DML inside `WITH`, you have to sort client-side or read the rows back in a separate query — which reintroduces the round trip you were avoiding. ## Which rows count as "modified" A row that the `WHERE` clause matched is updated even when the assigned value equals the value already there — `SET status = status` still rewrites the row, and that row appears in the `RETURNING` output. So do not read "came back from RETURNING" as "the value changed"; read it as "this row was targeted and written". If you want only genuinely changing rows, say so in the predicate: ```sql UPDATE customers SET tier = 'gold' WHERE annual_spend > 1000 AND tier <> 'gold' RETURNING customer_id; ``` Now the returned set is exactly the customers whose tier actually flipped, which is usually what a caller wants to act on — send a notification, enqueue a job, write an audit entry. ## Practical uses of the returned set Because you get identifiers rather than a count, the returned rows are directly actionable: they name the entities that changed, so downstream work can be driven from them. The same shape applies to `DELETE ... RETURNING`, where the returned rows are the last chance to see data that no longer exists in the table.