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.

Practice

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

easy

A. It delays the update until a commit is issued.

B. It prevents the update from modifying any rows.

C. It returns the rows that were updated immediately after the update.

D. It returns the number of rows in the table before the update.

Solution

1. Step 1: Understand the purpose of RETURNING

   The RETURNING clause is used to get the data of rows affected by an INSERT, UPDATE, or DELETE immediately.

2. Step 2: Apply to UPDATE statement

   When used with UPDATE, RETURNING shows the updated rows without needing a separate SELECT query.

3. Final Answer:

   It returns the rows that were updated immediately after the update. -> Option C

4. Quick Check:

   RETURNING shows changed rows = B [OK]

Hint: RETURNING shows changed rows right after modification [OK]

Common Mistakes:

• Thinking RETURNING delays changes
• Assuming RETURNING prevents updates
• Confusing RETURNING with row count only

2. Which of the following is the correct syntax to return all columns of updated rows in PostgreSQL?

easy

A. UPDATE table_name SET col = val RETURNING ALL COLUMNS;

B. UPDATE table_name SET col = val RETURNING *;

C. UPDATE table_name SET col = val RETURNING ALL;

D. UPDATE table_name SET col = val RETURN *;

Solution

1. Step 1: Recall correct RETURNING syntax

   PostgreSQL uses RETURNING followed by column names or * to return all columns.

2. Step 2: Identify correct option

   Only UPDATE table_name SET col = val RETURNING *; uses RETURNING * which is valid syntax to return all columns.

3. Final Answer:

   UPDATE table_name SET col = val RETURNING *; -> Option B

4. Quick Check:

   RETURNING * returns all columns [OK]

Hint: Use RETURNING * to get all columns after update [OK]

Common Mistakes:

• Using RETURN instead of RETURNING
• Writing RETURNING ALL instead of RETURNING *
• Adding extra keywords like ALL COLUMNS

3. Given the table users(id INT, name TEXT, age INT) with rows:
(1, 'Alice', 30), (2, 'Bob', 25)
What will this query return?

UPDATE users SET age = age + 1 WHERE id = 2 RETURNING id, age;

medium

A. (2, 26)

B. (2, 25)

C. (1, 31)

D. (1, 30)

Solution

1. Step 1: Identify updated row

   The WHERE clause updates only the row with id = 2 (Bob).

2. Step 2: Calculate new age and returned columns

   Bob's age increases from 25 to 26. RETURNING id, age returns (2, 26).

3. Final Answer:

   (2, 26) -> Option A

4. Quick Check:

   Updated row with id=2 returns new age 26 [OK]

Hint: RETURNING shows updated values immediately [OK]

Common Mistakes:

• Returning old values instead of updated
• Confusing which row is updated
• Ignoring WHERE clause effect

4. What is wrong with this query?

UPDATE products SET price = price * 1.1 RETURNING;

medium

A. RETURNING cannot be used with UPDATE statements.

B. The multiplication operator * is invalid in UPDATE.

C. Missing WHERE clause causes syntax error.

D. RETURNING clause requires columns or * to specify what to return.

Solution

1. Step 1: Check RETURNING syntax

   RETURNING must be followed by column names or *; empty RETURNING is invalid syntax.

2. Step 2: Validate other parts

   Multiplication and UPDATE without WHERE are valid; RETURNING with no columns is the error.

3. Final Answer:

   RETURNING clause requires columns or * to specify what to return. -> Option D

4. Quick Check:

   RETURNING needs columns or * [OK]

Hint: Always specify columns or * after RETURNING [OK]

Common Mistakes:

• Leaving RETURNING empty
• Thinking WHERE is mandatory for syntax
• Believing RETURNING disallowed in UPDATE

5. You want to increase the salary by 10% for employees in department 5 and get their id and new salary. Which query correctly does this and returns the updated rows?

hard

A. UPDATE employees SET salary = salary * 1.1 WHERE department_id = 5 RETURNING id, salary;

B. UPDATE employees SET salary = salary + 10 WHERE department_id = 5 RETURNING *;

C. UPDATE employees SET salary = salary * 1.1 RETURNING id, salary WHERE department_id = 5;

D. UPDATE employees SET salary = salary * 1.1 WHERE department_id = 5 RETURN id, salary;

Solution

1. Step 1: Identify correct UPDATE with WHERE and RETURNING

   The query must update only department 5 employees and return id and salary.

2. Step 2: Check syntax correctness

   UPDATE employees SET salary = salary * 1.1 WHERE department_id = 5 RETURNING id, salary; uses correct syntax: UPDATE ... SET ... WHERE ... RETURNING id, salary;

3. Step 3: Eliminate incorrect options

   Adding a fixed 10 instead of multiplying by 1.1 doesn't implement a 10% increase. Placing RETURNING before WHERE is invalid syntax. Using RETURN instead of RETURNING is wrong.

4. Final Answer:

   UPDATE employees SET salary = salary * 1.1 WHERE department_id = 5 RETURNING id, salary; -> Option A

5. Quick Check:

   Correct syntax with WHERE and RETURNING columns [OK]

Hint: RETURNING follows WHERE clause with columns listed [OK]

Common Mistakes:

• Placing RETURNING before WHERE
• Using RETURN instead of RETURNING
• Adding fixed amount instead of percentage