Returning modified rows with RETURNING in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What rows are returned by this UPDATE with RETURNING?

Consider a table employees with columns id, name, and salary. The table has these rows:

(1, 'Alice', 5000), (2, 'Bob', 6000), (3, 'Carol', 7000)

What rows will be returned by this query?

UPDATE employees SET salary = salary + 500 WHERE salary < 6500 RETURNING id, salary;

A (1, 5500), (2, 6500)

B (1, 5500), (2, 6500), (3, 7000)

C (3, 7000)

D No rows returned

💡 Hint

RETURNING returns only rows that were updated.

Only employees with salary less than 6500 are updated. Alice (5000) and Bob (6000) get their salary increased by 500. Carol (7000) is not updated, so not returned.

❓ query_result

intermediate

What does this DELETE with RETURNING output?

Given a table products with columns product_id and stock containing:

(101, 10), (102, 0), (103, 5)

What rows will be returned by this query?

DELETE FROM products WHERE stock = 0 RETURNING product_id;

A (102)

B (101), (103)

C (101), (102), (103)

D No rows returned

💡 Hint

RETURNING returns deleted rows matching the WHERE condition.

Only the product with stock 0 is deleted and returned. Others remain.

📝 Syntax

advanced

Which UPDATE with RETURNING syntax is correct?

You want to increase the price by 10 for all rows in items and return the item_id and new price. Which query is syntactically correct?

A UPDATE items SET price = price + 10 RETURNING * FROM items;

B UPDATE items SET price = price + 10 RETURN item_id, price;

C UPDATE items SET price = price + 10 RETURNING (item_id, price);

D UPDATE items SET price = price + 10 RETURNING item_id, price;

💡 Hint

RETURNING must be followed by column names without parentheses or extra keywords.

Option D uses correct syntax: RETURNING column list without parentheses or FROM clause. Others are invalid syntax.

❓ query_result

advanced

What is the output of this INSERT with RETURNING?

Given a table orders with columns order_id (serial primary key), customer, and amount, currently empty.

What will this query return?

INSERT INTO orders (customer, amount) VALUES ('John', 100), ('Jane', 150) RETURNING order_id, amount;

A (NULL, 100), (NULL, 150)

B (0, 100), (1, 150)

C (1, 100), (2, 150)

D No rows returned

💡 Hint

RETURNING returns inserted rows including generated serial keys.

The serial order_id starts at 1 and increments. The query inserts two rows and returns their order_id and amount.

🧠 Conceptual

expert

Why use RETURNING in data modification queries?

Which is the main advantage of using RETURNING in UPDATE, DELETE, or INSERT statements in PostgreSQL?

A It speeds up the query execution by skipping transaction logging.

B It allows retrieving the affected rows immediately without a separate SELECT query.

C It automatically commits the transaction after the modification.

D It prevents other users from accessing the modified rows.

💡 Hint

Think about how to get data about changed rows efficiently.

RETURNING lets you get the changed rows' data right away, avoiding extra queries. It does not affect transaction control or locking.