UPDATE with RETURNING clause in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of UPDATE with RETURNING clause

Given the table employees with columns id, name, and salary, what is the output of this query?

UPDATE employees SET salary = salary + 1000 WHERE id = 3 RETURNING id, salary;

PostgreSQL

UPDATE employees SET salary = salary + 1000 WHERE id = 3 RETURNING id, salary;

A [{ "id": 3, "salary": 6000 }]

B [{ "id": 3, "salary": 5000 }]

C []

D SyntaxError

💡 Hint

The RETURNING clause outputs the updated rows after the UPDATE.

The query increases salary by 1000 for employee with id 3. If original salary was 5000, new salary is 6000. RETURNING shows the updated row.

🧠 Conceptual

intermediate

Purpose of RETURNING clause in UPDATE

What is the main purpose of using the RETURNING clause in an UPDATE statement in PostgreSQL?

A To return the rows that were updated with their new values

B To rollback the update if a condition fails

C To update multiple tables at once

D To create a backup of the updated rows

💡 Hint

Think about what you want to see immediately after updating rows.

RETURNING lets you see the updated rows and their new values without running a separate SELECT.

📝 Syntax

advanced

Identify the syntax error in UPDATE with RETURNING

Which of the following UPDATE statements with RETURNING clause is syntactically correct?

A UPDATE products SET price = price * 1.1 RETURNING;

B UPDATE products SET price = price * 1.1 RETURNING id, price;

C UPDATE products SET price = price * 1.1 RETURNING id price;

D UPDATE products SET price = price * 1.1 RETURNING id, price

💡 Hint

RETURNING must specify columns or * and end with semicolon.

Option B correctly lists columns and ends with semicolon. B misses columns. C misses comma. D misses semicolon.

❓ optimization

advanced

Optimizing UPDATE with RETURNING for multiple rows

You want to update salaries for all employees in department 5 by 10% and get their new salaries. Which query is the most efficient?

A UPDATE employees SET salary = salary * 1.1 WHERE department_id = 5; SELECT id, salary FROM employees WHERE department_id = 5;

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

C SELECT id, salary FROM employees WHERE department_id = 5; UPDATE employees SET salary = salary * 1.1 WHERE department_id = 5;

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

💡 Hint

Consider how many queries run and how data is returned.

Option D updates and returns updated rows in one query. A runs two queries. C returns old salaries before update. D has syntax error.

🔧 Debug

expert

Error caused by incorrect RETURNING usage

What error will this query produce?

UPDATE orders SET status = 'shipped' RETURNING WHERE order_date < '2024-01-01';

A No error, query runs successfully

B RuntimeError: invalid date format

C SyntaxError: missing columns after RETURNING

D TypeError: wrong data type in SET clause

💡 Hint

RETURNING must be followed by columns or *.

RETURNING clause requires columns or * after it. Here it is empty, causing syntax error.