Returning modified rows with RETURNING in PostgreSQL - Step-by-Step Execution

Concept Flow - Returning modified rows with RETURNING

Start UPDATE/DELETE/INSERT

↓

Modify rows in table

↓

RETURNING clause activated?

No→No rows returned

Yes↓

Collect modified rows

↓

Return rows to client

When you run an UPDATE, DELETE, or INSERT with RETURNING, the database modifies rows and then sends back the changed rows to you.

Execution Sample

PostgreSQL

UPDATE employees
SET salary = salary * 1.1
WHERE department = 'Sales'
RETURNING id, salary;

This query increases salary by 10% for Sales department employees and returns their id and new salary.

Execution Table

Step	Row ID	Original Salary	Condition (department='Sales')	Action	Returned Row
1	101	50000	True	Salary updated to 55000	id=101, salary=55000
2	102	48000	False	No change	No
3	103	52000	True	Salary updated to 57200	id=103, salary=57200
4	104	47000	False	No change	No
5	105	51000	True	Salary updated to 56100	id=105, salary=56100
6	-	-	-	-	Execution ends: all rows checked

💡 All rows processed; only rows matching condition updated and returned.

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
salary for id=101	50000	55000	55000	55000	55000
salary for id=102	48000	48000	48000	48000	48000
salary for id=103	52000	52000	57200	57200	57200
salary for id=104	47000	47000	47000	47000	47000
salary for id=105	51000	51000	51000	56100	56100

Key Moments - 2 Insights

Why do some rows not appear in the RETURNING output even though they exist in the table?

Only rows that meet the WHERE condition and are modified by the UPDATE appear in RETURNING, as shown in execution_table rows 2 and 4 where condition is False and no rows are returned.

Does RETURNING show the old values or the new updated values?

RETURNING shows the new updated values after the modification, as seen in execution_table rows 1, 3, and 5 where salaries are increased.

Visual Quiz - 3 Questions

Look at the execution_table, what is the salary of employee with id=103 after the update?

A 5200

B 52000

C 57200

D None

💡 Hint

Check the 'Returned Row' and 'Action' columns for row 3 in execution_table.

At which step does the condition department='Sales' become false?

A Step 2

B Step 3

C Step 1

D Step 5

💡 Hint

Look at the 'Condition' column in execution_table rows to find where it is False.

If the RETURNING clause was removed, what would change in the execution?

A Modified rows would still be returned

B No rows would be returned after update

C The update would not happen

D Only the first row would be returned

💡 Hint

RETURNING controls what rows are sent back; without it, no rows are returned.

Concept Snapshot

UPDATE/DELETE/INSERT with RETURNING
modifies rows then returns those rows
RETURNING shows new values after change
Only modified rows matching condition return
Useful to get updated data immediately

Full Transcript

This visual execution shows how PostgreSQL processes an UPDATE with RETURNING. First, it checks each row in the table. If the row matches the condition (department='Sales'), it updates the salary by 10%. Then, because of RETURNING, it sends back the id and new salary of those updated rows. Rows not matching the condition are not changed and not returned. The variable tracker shows salary changes step by step. Key moments clarify why only some rows return and that RETURNING shows new values. The quiz tests understanding of these steps.