Conditional INSERT with ON CONFLICT in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this INSERT with ON CONFLICT?

Consider a table users(id SERIAL PRIMARY KEY, username TEXT UNIQUE, score INT). You run this query:

INSERT INTO users (username, score) VALUES ('alice', 10) ON CONFLICT (username) DO UPDATE SET score = users.score + 5 RETURNING score;

Assuming the table initially has a row {id:1, username:'alice', score:7}, what will be the returned score?

PostgreSQL

INSERT INTO users (username, score) VALUES ('alice', 10) ON CONFLICT (username) DO UPDATE SET score = users.score + 5 RETURNING score;

A 15

B 10

C 7

D 12

💡 Hint

ON CONFLICT updates the existing row's score by adding 5 to the current score.

The existing score is 7. The update adds 5, so the new score is 12. But the query uses users.score which refers to the old value, so the update sets score = 7 + 5 = 12. The RETURNING clause returns the updated score, which is 12.

Correction: The correct updated score is 12, so option D (15) is incorrect. The correct answer is C (12).

❓ query_result

intermediate

What happens if ON CONFLICT DO NOTHING is used?

Given the same users table, what will be the result of this query if a user with username 'bob' already exists?

INSERT INTO users (username, score) VALUES ('bob', 20) ON CONFLICT (username) DO NOTHING RETURNING *;

PostgreSQL

INSERT INTO users (username, score) VALUES ('bob', 20) ON CONFLICT (username) DO NOTHING RETURNING *;

A Returns no rows

B Returns the newly inserted row with score 20

C Returns the existing row for 'bob'

D Raises a unique constraint violation error

💡 Hint

DO NOTHING skips the insert if conflict occurs.

ON CONFLICT DO NOTHING skips the insert and returns no rows if the username already exists. So RETURNING * returns no rows.

📝 Syntax

advanced

Which query correctly uses ON CONFLICT to update only if new score is higher?

Choose the correct query that inserts a user or updates the score only if the new score is greater than the existing score.

A INSERT INTO users (username, score) VALUES ('carol', 15) ON CONFLICT (username) DO UPDATE SET score = EXCLUDED.score IF EXCLUDED.score > users.score;

B INSERT INTO users (username, score) VALUES ('carol', 15) ON CONFLICT (username) DO UPDATE SET score = EXCLUDED.score WHERE EXCLUDED.score > users.score;

C INSERT INTO users (username, score) VALUES ('carol', 15) ON CONFLICT (username) DO UPDATE SET score = CASE WHEN EXCLUDED.score > users.score THEN EXCLUDED.score ELSE users.score END;

D INSERT INTO users (username, score) VALUES ('carol', 15) ON CONFLICT (username) DO UPDATE SET score = EXCLUDED.score WHERE users.score < EXCLUDED.score;

💡 Hint

Use WHERE clause after DO UPDATE to conditionally update.

Option B uses the correct syntax: the WHERE clause after DO UPDATE limits the update to cases where the new score is higher.

Option B uses invalid IF syntax.

Option B is valid SQL but does not prevent update if new score is lower; it always updates.

Option B has the WHERE clause reversed but is syntactically valid; however, it is equivalent to D logically.

Between A and D, D is the canonical correct syntax.

❓ optimization

advanced

How to optimize bulk insert with ON CONFLICT for large data?

You want to insert thousands of rows into users with ON CONFLICT to update scores. Which approach is best for performance?

A Delete conflicting rows first, then insert all rows without ON CONFLICT

B Insert rows one by one with ON CONFLICT DO UPDATE in a loop

C Use a single INSERT with multiple VALUES and ON CONFLICT DO UPDATE

D Insert all rows into a temporary table, then run an UPDATE joining temp and users

💡 Hint

Batch operations reduce overhead.

Option C is best because a single INSERT with multiple VALUES and ON CONFLICT DO UPDATE minimizes transaction overhead and uses PostgreSQL's efficient upsert.

Option C is slow due to many round-trips.

Option C adds complexity and may not be faster.

Option C risks data loss and is inefficient.

🧠 Conceptual

expert

Why does ON CONFLICT DO UPDATE sometimes cause deadlocks?

In concurrent transactions, using ON CONFLICT DO UPDATE can cause deadlocks. What is the main reason?

A Because conflicting rows are locked in different orders by concurrent transactions

B Because ON CONFLICT DO UPDATE locks the entire table during insert

C Because ON CONFLICT DO UPDATE disables indexes causing full scans

D Because PostgreSQL does not support concurrent inserts with ON CONFLICT

💡 Hint

Think about row locking order in concurrent updates.

Deadlocks occur when concurrent transactions lock rows in different orders, causing circular waits. ON CONFLICT DO UPDATE locks the conflicting rows, so if two transactions try to update the same rows in different orders, deadlocks can happen.

Option A is false; table locks are not taken.

Option A is false; indexes are used.

Option A is false; PostgreSQL supports concurrent inserts with ON CONFLICT.