Returning modified rows with RETURNING in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the RETURNING clause do in a PostgreSQL UPDATE statement?

It returns the rows that were modified by the UPDATE, showing their new values immediately after the change.

beginner

How can RETURNING help when you want to see the new values of rows after an INSERT?

RETURNING lets you get the inserted rows' data right away without running a separate SELECT query.

intermediate

Can RETURNING be used with DELETE statements in PostgreSQL? What does it return?

Yes, RETURNING can be used with DELETE to return the rows that were deleted, showing their values before removal.

beginner

Write a simple UPDATE query that changes a user's email and returns the updated row using RETURNING.

UPDATE users SET email = '[e-mail]' WHERE id = 1 RETURNING *;

intermediate

Why is RETURNING useful in applications that modify data?

It saves time and resources by combining data modification and retrieval in one step, avoiding extra queries.

What does the RETURNING clause do in a PostgreSQL UPDATE statement?

A Prevents the update from happening

B Returns the modified rows after the update

C Deletes the rows after updating

D Creates a backup of the table

RETURNING returns the rows that were changed by the UPDATE statement.

Which SQL statements can use RETURNING in PostgreSQL?

A INSERT, UPDATE, DELETE

B SELECT only

C CREATE and DROP

D ALTER only

RETURNING works with INSERT, UPDATE, and DELETE to return affected rows.

What will this query return? UPDATE products SET price = price * 1.1 RETURNING id, price;

A Only the old prices before update

B Nothing, it only updates

C The id and new price of all updated products

D An error because RETURNING is invalid here

RETURNING returns the specified columns of updated rows with new values.

Why might RETURNING improve application performance?

A It combines update and retrieval in one query

B It caches data automatically

C It compresses the database

D It disables logging

RETURNING avoids extra SELECT queries by returning data immediately after modification.

What happens if you use RETURNING * in a DELETE statement?

A It causes a syntax error

B It returns nothing

C It returns the rows after deletion

D It returns all columns of the deleted rows

RETURNING * returns all columns of rows before they are deleted.

Explain how the RETURNING clause works in PostgreSQL and give an example of its use in an UPDATE statement.

Think about how to get updated rows back immediately.

Describe the benefits of using RETURNING in data modification queries in PostgreSQL.

Consider what happens without RETURNING and why it matters.