Sometimes it is useful to obtain data from modified rows while
they are being manipulated. The
DELETE commands all have an optional
RETURNING clause that supports this.
RETURNING avoids performing
an extra database query to collect the data, and is especially
valuable when it would otherwise be difficult to identify the
modified rows reliably.
The allowed contents of a
RETURNING clause are the same as a
SELECT command's output list (see Section 7.3). It can contain
column names of the command's target table, or value expressions
using those columns. A common shorthand is
RETURNING *, which selects all columns of the
target table in order.
INSERT, the data available
RETURNING is the row as it was
inserted. This is not so useful in trivial inserts, since it
would just repeat the data provided by the client. But it can be
very handy when relying on computed default values. For example,
when using a
column to provide unique identifiers,
RETURNING can return the ID assigned to a new
CREATE TABLE users (firstname text, lastname text, id serial primary key); INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;
RETURNING clause is also very
INSERT ... SELECT.
UPDATE, the data available
RETURNING is the new content of
the modified row. For example:
UPDATE products SET price = price * 1.10 WHERE price <= 99.99 RETURNING name, price AS new_price;
DELETE, the data available
RETURNING is the content of the
deleted row. For example:
DELETE FROM products WHERE obsoletion_date = 'today' RETURNING *;
If there are triggers (Chapter 39) on
the target table, the data available to
RETURNING is the row as modified by the
triggers. Thus, inspecting columns computed by triggers is
another common use-case for
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.