Return changed rows

After an insert, your application usually needs to know what the database created: the generated identifier, a default timestamp, a computed value. The naive pattern is to write, then immediately read back. PostgreSQL can return values directly from an insert, update, or delete instead:

INSERT INTO notes (title)
VALUES ('Plan the week')
RETURNING id, title;

 id |     title
----+---------------
  4 | Plan the week
(1 row)

INSERT 0 1

This keeps the change and its result in one statement. One round trip instead of two, and no race: a separate SELECT max(id) after the insert can pick up somebody else’s row on a busy system. RETURNING gives you exactly the rows this statement touched.

It works on UPDATE and DELETE too

RETURNING accepts any expression over the affected rows, so updates can hand back their new values:

UPDATE notes
SET title = 'Plan Monday'
WHERE id = 4
RETURNING id, title;

And deletes can hand back what they removed:

DELETE FROM notes
WHERE created_at < now() - interval '1 year'
RETURNING id;

The delete variant doubles as an audit trail: you know precisely which rows a cleanup job removed, which beats logging “deleted some old rows”.

Read the row count

Look at the status line psql prints, or the row count your driver reports. It tells you how many rows the statement actually changed, and RETURNING shows you which ones.

Zero rows is the quiet failure mode. An UPDATE whose WHERE matched nothing succeeds with an empty result, no error. Application code that assumes “no exception means it worked” ships bugs this way. Check that RETURNING produced the row you expected.

The opposite direction is more painful. Forget the WHERE entirely and the statement updates every row in the table, then dutifully returns all of them. If an update you expected to touch one row starts streaming back thousands, that flood of output is your earliest warning, and a transaction you can still ROLLBACK is your way out. Wrapping risky writes in an explicit transaction is covered in the transactions lesson; RETURNING is what makes the damage visible before you commit it.