RETURNING Clause

Objectives

By the end of this lesson, you should be able to:

• Use RETURNING to see the rows an INSERT, UPDATE, or DELETE just affected
• Explain why this is useful without running a separate SELECT

💡 Why this matters: INSERT, UPDATE, and DELETE change data, but don’t normally show you the result. RETURNING gives it back immediately, in the same statement, no follow-up SELECT required.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table from Module 3, filtered with WHERE (covered fully in Module 6).

RETURNING on INSERT

INSERT INTO employees (first_name, last_name, email, department, salary)
VALUES ('Sam', 'Ortiz', 'sam.ortiz@example.com', 'Sales', 60000)
RETURNING id, first_name, hire_date;

 id | first_name | hire_date
----+------------+------------
  5 | Sam        | 2026-07-28

This is especially useful for INSERT, since id (a SERIAL column, from Module 3) and hire_date (a DEFAULT, also from Module 3) are both generated by PostgreSQL, not supplied in the INSERT. RETURNING hands them straight back, without a separate query to look the new row back up.

RETURNING on UPDATE

UPDATE employees SET salary = salary * 1.05 WHERE department = 'Engineering'
RETURNING first_name, salary;

 first_name | salary
------------+----------
 Erin       | 89250.00
 Maya       | 85050.00

RETURNING here shows the new values, after the update was applied, for every row that matched, confirming exactly what changed without a follow-up SELECT.

RETURNING on DELETE

DELETE FROM employees WHERE first_name = 'Sam'
RETURNING first_name, last_name;

 first_name | last_name
------------+-----------
 Sam        | Ortiz

Since a DELETE removes the row, this is the last chance to see its data, RETURNING here effectively confirms exactly what was just deleted.

RETURNING *

Just like SELECT *, RETURNING * returns every column instead of listing specific ones:

DELETE FROM employees WHERE first_name = 'Priya' RETURNING *;

Useful when you want to log or confirm the entire row, not just a couple of fields.

Try It

1. Insert a new employee with RETURNING id, first_name, and confirm the generated id comes back immediately.
2. Update one employee’s salary with RETURNING first_name, salary, and confirm the new value is shown.
3. Delete one employee with RETURNING *, and confirm every column of the deleted row is shown.

Recap

• RETURNING shows the affected rows from an INSERT, UPDATE, or DELETE, without a separate SELECT.
• On INSERT, it reveals generated values like a SERIAL id or a DEFAULT.
• On UPDATE, it shows the new values after the change. On DELETE, it shows the data that was just removed.

Next lesson: this module’s exercises, building a small employee database with every CRUD operation from this module.