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

Challenge - 5 Problems

❓ query_result

intermediate

What does this INSERT with RETURNING output?

Given the table users(id SERIAL PRIMARY KEY, name TEXT, age INT), what is 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 [{"name": "Alice", "age": 30}]

B [{"id": 1, "name": "Alice"}]

C No output, just inserts the row

D Syntax error

💡 Hint

The RETURNING clause returns the specified columns of the inserted row.

The RETURNING clause outputs the columns requested from the newly inserted row. Here, it returns the generated id and the name 'Alice'.

📝 Syntax

intermediate

Which INSERT with RETURNING syntax is correct?

Choose the correct syntax to insert a row and return the generated id and age from table people(id SERIAL, age INT).

A INSERT INTO people (age) VALUES (25) RETURNING id, age;

B INSERT INTO people (age) VALUES (25) RETURN id, age;

C INSERT INTO people (age) VALUES (25) RETURNING (id, age);

D INSERT INTO people (age) VALUES (25) OUTPUT id, age;

💡 Hint

The keyword is RETURNING without parentheses.

PostgreSQL uses RETURNING without parentheses to specify columns to return after an INSERT.

❓ optimization

advanced

Why use INSERT with RETURNING instead of separate SELECT?

You want to insert a new product and get its generated id immediately. Which is the main advantage of using INSERT ... RETURNING id over inserting then running a separate SELECT to get the id?

A It returns all columns by default without specifying.

B It automatically commits the transaction.

C It locks the entire table to prevent other inserts.

D It reduces the number of queries and avoids race conditions.

💡 Hint

Think about efficiency and data consistency.

Using RETURNING returns the inserted row's data immediately, avoiding extra queries and potential conflicts.

🔧 Debug

advanced

What error does this INSERT with RETURNING cause?

Given table orders(order_id SERIAL PRIMARY KEY, amount INT NOT NULL), what error occurs with this query?

INSERT INTO orders (amount) VALUES (NULL) RETURNING order_id;

A Syntax error near RETURNING

B ERROR: duplicate key value violates unique constraint

C ERROR: null value in column "amount" violates not-null constraint

D No error, returns order_id

💡 Hint

Check the NOT NULL constraint on amount.

Inserting NULL into a NOT NULL column causes a constraint violation error.

🧠 Conceptual

expert

How does RETURNING behave with multi-row INSERT?

Consider this query inserting multiple rows:

INSERT INTO employees(name, salary) VALUES ('John', 5000), ('Jane', 6000) RETURNING id, name;

What will the RETURNING clause output?

A A result set with two rows, each containing the id and name of the inserted employees.

B An error because RETURNING cannot be used with multiple rows.

C No output because RETURNING works only for single-row inserts.

D Only the id and name of the first inserted row.

💡 Hint

RETURNING returns data for all inserted rows.

RETURNING outputs one row per inserted row, showing requested columns for each.