RETURNING clause mental model in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What does this INSERT with RETURNING output?

Consider the table users(id SERIAL PRIMARY KEY, name TEXT, age INT). What will be the output of this query?

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

PostgreSQL

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

A A single row with columns <code>id</code> and <code>name</code> showing the new user's id and name.

B A list of all users with their <code>id</code> and <code>name</code>.

C An error because RETURNING cannot be used with INSERT.

D No output; the query only inserts data silently.

💡 Hint

RETURNING lets you get back data from the rows you just inserted.

The RETURNING clause returns the specified columns from the newly inserted row(s). Here, it returns the new user's id and name.

❓ query_result

intermediate

What does this UPDATE with RETURNING output?

Given the table products(id INT PRIMARY KEY, price NUMERIC), what will this query output?

UPDATE products SET price = price * 1.1 WHERE id = 5 RETURNING id, price;

PostgreSQL

UPDATE products SET price = price * 1.1 WHERE id = 5 RETURNING id, price;

A An error because RETURNING is not allowed with UPDATE.

B The entire products table with updated prices.

C No output; UPDATE does not return rows.

D The <code>id</code> and updated <code>price</code> of the product with id 5.

💡 Hint

RETURNING shows the rows that were changed.

RETURNING returns the columns of the rows that were updated. Here, only the product with id 5 is updated and returned.

📝 Syntax

advanced

Which query correctly uses RETURNING with DELETE?

You want to delete a user with id 10 and get back their name. Which query is correct?

A DELETE FROM users WHERE id = 10 RETURNING name;

B DELETE FROM users RETURNING name WHERE id = 10;

C DELETE RETURNING name FROM users WHERE id = 10;

D DELETE FROM users WHERE id = 10; RETURNING name;

💡 Hint

RETURNING comes right after the WHERE clause in DELETE.

The correct syntax places RETURNING after the WHERE clause in DELETE statements.

❓ optimization

advanced

Why use RETURNING instead of a separate SELECT after INSERT?

You want to insert a row and get its generated id. Why is using RETURNING better than inserting then selecting?

A RETURNING locks the table, preventing other queries.

B RETURNING duplicates the inserted row in the table.

C RETURNING returns the data immediately without extra queries, improving performance.

D Using RETURNING causes the insert to fail if the SELECT fails.

💡 Hint

Think about how many queries you send to the database.

RETURNING lets you get data from the inserted row in the same query, avoiding extra round trips and improving speed.

🧠 Conceptual

expert

What happens if RETURNING is used with a statement that affects zero rows?

If you run UPDATE users SET age = age + 1 WHERE id = 999 RETURNING id; but no user has id 999, what is the output?

PostgreSQL

UPDATE users SET age = age + 1 WHERE id = 999 RETURNING id;

A The id 999 is returned anyway.

B An empty result set with zero rows returned.

C An error saying no rows were updated.

D The query hangs waiting for a matching row.

💡 Hint

Think about what happens when no rows match the WHERE condition.

RETURNING returns rows affected by the statement. If no rows match, it returns zero rows without error.