INSERT with RETURNING clause in PostgreSQL - Step-by-Step Execution

Concept Flow - INSERT with RETURNING clause

Start INSERT command

↓

Insert new row into table

↓

RETURNING clause triggers

↓

Return specified columns of inserted row

↓

Display returned data as query result

↓

End

The INSERT command adds a new row, then RETURNING outputs specified columns from that new row as the query result.

Execution Sample

PostgreSQL

INSERT INTO users (name, age) VALUES ('Alice', 30) RETURNING id, name;

Insert a new user named Alice aged 30, then return the new user's id and name.

Execution Table

Step	Action	Table State	RETURNING Output
1	Start INSERT command	users table unchanged
2	Insert row (name='Alice', age=30)	users table + new row (id=auto)
3	RETURNING clause fetches id and name	users table with new row	id=101, name='Alice'
4	Query returns the new row's id and name	users table with new row	id=101, name='Alice'
5	End of execution	users table with new row

💡 Insertion complete and RETURNING clause output displayed as query result

Variable Tracker

Variable	Start	After Insert	After RETURNING	Final
new_row	null	{name: 'Alice', age: 30, id: 101}	{id: 101, name: 'Alice'}	{id: 101, name: 'Alice'}

Key Moments - 2 Insights

Why do we get output from an INSERT query when using RETURNING?

Because the RETURNING clause tells PostgreSQL to send back specified columns from the newly inserted row as the query result, as shown in execution_table rows 3 and 4.

Does the RETURNING clause change the data inserted into the table?

No, RETURNING only outputs data after insertion; it does not modify the inserted data. The table state changes only at step 2 in the execution_table.

Visual Quiz - 3 Questions

Look at the execution_table at step 3, what does the RETURNING clause output?

A The id and name of the inserted row

B The entire users table

C Only the id column of all rows

D No output, just inserts data

💡 Hint

Check the 'RETURNING Output' column at step 3 in the execution_table

At which step does the new row actually get added to the users table?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Look at the 'Table State' column in the execution_table to see when the table changes

If we remove the RETURNING clause, what changes in the execution_table?

A No row is inserted

B No output is returned after insertion

C The table state does not change

D The inserted row is returned by default

💡 Hint

RETURNING clause controls output; without it, no data is returned after insert

Concept Snapshot

INSERT INTO table (columns) VALUES (values) RETURNING columns;
- Inserts a new row into the table.
- RETURNING outputs specified columns from the inserted row.
- Useful to get auto-generated IDs or inserted data immediately.
- Without RETURNING, INSERT returns no rows.
- Works only in PostgreSQL and some other DBs supporting RETURNING.

Full Transcript

This visual execution shows how the INSERT statement with RETURNING clause works in PostgreSQL. First, the INSERT command starts and adds a new row to the users table with the given values. Then, the RETURNING clause fetches specified columns from the newly inserted row, such as id and name. This data is returned as the query result. The table state changes only when the row is inserted, and RETURNING does not modify data but only outputs it. This helps to immediately get values like auto-generated IDs after insertion.