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.

Practice

1. What does the ON CONFLICT DO NOTHING clause do in a PostgreSQL INSERT statement?

easy

A. It skips inserting the row if a conflict occurs on a unique key.

B. It updates the existing row with new values on conflict.

C. It deletes the conflicting row before inserting the new one.

D. It raises an error and stops the insert operation.

Solution

1. Step 1: Understand ON CONFLICT behavior

   The ON CONFLICT DO NOTHING clause tells PostgreSQL to ignore the insert if a duplicate key conflict happens.

2. Step 2: Compare with other conflict actions

   Unlike DO UPDATE which modifies existing rows, DO NOTHING simply skips the insert without error.

3. Final Answer:

   It skips inserting the row if a conflict occurs on a unique key. -> Option A

4. Quick Check:

   ON CONFLICT DO NOTHING = skip insert on duplicate [OK]

Hint: DO NOTHING skips duplicates silently, no error thrown [OK]

Common Mistakes:

• Thinking DO NOTHING updates existing rows
• Assuming DO NOTHING deletes conflicting rows
• Believing DO NOTHING raises an error

2. Which of the following is the correct syntax to insert a row and update the existing row on conflict for the column id in PostgreSQL?

easy

A. INSERT INTO table_name (id, val) VALUES (1, 'a') ON DUPLICATE KEY UPDATE val = VALUES(val);

B. INSERT INTO table_name (id, val) VALUES (1, 'a') ON CONFLICT (id) DO NOTHING SET val = EXCLUDED.val;

C. INSERT INTO table_name (id, val) VALUES (1, 'a') ON CONFLICT UPDATE val = EXCLUDED.val;

D. INSERT INTO table_name (id, val) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET val = EXCLUDED.val;

Solution

1. Step 1: Identify correct ON CONFLICT syntax

   PostgreSQL uses ON CONFLICT (column) DO UPDATE SET to update on conflict.

2. Step 2: Check syntax details

   INSERT INTO table_name (id, val) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET val = EXCLUDED.val; correctly uses EXCLUDED.val to refer to the new value and specifies the conflict column.

3. Final Answer:

   INSERT INTO table_name (id, val) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET val = EXCLUDED.val; -> Option D

4. Quick Check:

   ON CONFLICT (id) DO UPDATE SET val = EXCLUDED.val [OK]

Hint: Use ON CONFLICT (column) DO UPDATE SET col = EXCLUDED.col [OK]

Common Mistakes:

• Using MySQL syntax ON DUPLICATE KEY UPDATE
• Omitting conflict target column in ON CONFLICT
• Combining DO NOTHING with SET clause incorrectly

3. Given the table users(id SERIAL PRIMARY KEY, username TEXT UNIQUE, score INT), what will be the result of this query?

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

Assuming a row with username 'alice' and score 5 already exists.

medium

A. A new row with username 'alice' and score 10 is inserted.

B. The existing row's score is updated to 15 and returned.

C. The insert fails with a duplicate key error.

D. The existing row is deleted and a new row inserted.

Solution

1. Step 1: Understand ON CONFLICT DO UPDATE behavior

   The query updates the existing row where username='alice' because of the conflict on username.

2. Step 2: Calculate updated score

   The update sets score = users.score + EXCLUDED.score = 5 + 10 = 15, and returns the updated row.

3. Final Answer:

   The existing row's score is updated to 15 and returned. -> Option B

4. Quick Check:

   ON CONFLICT DO UPDATE adds scores = 15 [OK]

Hint: ON CONFLICT DO UPDATE can use existing and new values [OK]

Common Mistakes:

• Thinking a new row is inserted despite conflict
• Expecting an error on duplicate key
• Assuming the row is deleted before insert

4. Given the table products(id INT UNIQUE, name TEXT UNIQUE), identify the error in this PostgreSQL query:

INSERT INTO products (id, name) VALUES (1, 'Pen') ON CONFLICT DO UPDATE SET name = EXCLUDED.name;

medium

A. Missing conflict target column after ON CONFLICT.

B. EXCLUDED keyword is not valid in DO UPDATE.

C. VALUES clause should be after DO UPDATE.

D. DO UPDATE cannot be used without WHERE clause.

Solution

1. Step 1: Check ON CONFLICT syntax

   When using DO UPDATE, PostgreSQL requires specifying the conflict target column or constraint.

2. Step 2: Identify missing conflict target

   The query lacks (id) or constraint name after ON CONFLICT, causing syntax error.

3. Final Answer:

   Missing conflict target column after ON CONFLICT. -> Option A

4. Quick Check:

   ON CONFLICT needs target column for DO UPDATE [OK]

Hint: Always specify conflict column with DO UPDATE [OK]

Common Mistakes:

• Omitting conflict target column
• Misusing EXCLUDED keyword
• Assuming WHERE clause is mandatory

5. You want to insert a new user with email and last_login into a table users(email TEXT UNIQUE, last_login TIMESTAMP). If the email already exists, update last_login only if the new timestamp is more recent. Which query correctly implements this conditional update?

hard

A. INSERT INTO users (email, last_login) VALUES ('[e-mail]', '2024-01-01') ON CONFLICT DO NOTHING WHERE EXCLUDED.last_login > users.last_login;

B. INSERT INTO users (email, last_login) VALUES ('[e-mail]', '2024-01-01') ON CONFLICT (email) DO UPDATE SET last_login = users.last_login WHERE EXCLUDED.last_login > users.last_login;

C. INSERT INTO users (email, last_login) VALUES ('[e-mail]', '2024-01-01') ON CONFLICT (email) DO UPDATE SET last_login = EXCLUDED.last_login WHERE EXCLUDED.last_login > users.last_login;

D. INSERT INTO users (email, last_login) VALUES ('[e-mail]', '2024-01-01') ON CONFLICT (email) DO UPDATE SET last_login = EXCLUDED.last_login;

Solution

1. Step 1: Understand conditional update with WHERE clause

   PostgreSQL allows a WHERE clause in DO UPDATE to conditionally apply the update.

2. Step 2: Check condition for updating last_login

   INSERT INTO users (email, last_login) VALUES ('[e-mail]', '2024-01-01') ON CONFLICT (email) DO UPDATE SET last_login = EXCLUDED.last_login WHERE EXCLUDED.last_login > users.last_login; updates last_login only if the new timestamp is greater than the existing one, using WHERE EXCLUDED.last_login > users.last_login.

3. Final Answer:

   INSERT INTO users (email, last_login) VALUES ('[e-mail]', '2024-01-01') ON CONFLICT (email) DO UPDATE SET last_login = EXCLUDED.last_login WHERE EXCLUDED.last_login > users.last_login; -> Option C

4. Quick Check:

   Use WHERE in DO UPDATE for conditional update [OK]

Hint: Use WHERE in DO UPDATE to update conditionally [OK]

Common Mistakes:

• Forgetting WHERE clause for conditional update
• Using DO NOTHING with WHERE incorrectly
• Updating unconditionally without checking timestamps