阅读中文版本

The RETURNING Clause

RETURNING lets a modifying statement hand back column values of the affected rows, removing the extra round trip that follows an insert or update.

Definition

RETURNING is a SQL extension on INSERT, UPDATE, and DELETE that returns expressions computed from the rows the statement inserted, updated, or removed.

It is used to fetch generated identifiers, defaults, and server-side computed values immediately after a write. Compared to simpler alternatives, this concept fits best when the trade-off it represents is the one the system actually cares about.

When to use it

A common use case is applying it at the boundary where the cost of getting it wrong is highest, and the scenario usually involves a concrete guarantee the system must uphold. Unlike an ad hoc approach, it makes that guarantee explicit, reviewable, and testable.

How it works

For inserts it avoids a follow-up query for a generated primary key or a default such as a creation timestamp, halving the round trips for that write.

For updates and deletes it returns only the rows actually affected, which is valuable when the statement has a WHERE clause that might match nothing.

Trade-offs and alternatives

Portability varies, so code intended for more than one engine should isolate the clause behind a repository layer rather than spreading it across the schema. The main drawback is the extra complexity it introduces, so the benefit should clearly outweigh that cost before a team adopts it.

Common misconceptions

A persistent misconception is that returning returns every row that matched the update. Another is that the clause is available on every database engine. For example, an engineer who assumes otherwise may design around a guarantee that does not actually exist, which only becomes obvious under load or failure.

Anti-patterns to avoid

Two recurring anti-patterns are to issuing a second select to retrieve a just-generated key and to using returning inside a statement that must run on a database lacking support. Both tend to surface as hard-to-debug production issues rather than obvious test failures. Related concepts include GIN Indexes for Composite Values, Trigram Indexes for Fuzzy Matching, Full-Text Search Vectors.