Conditional INSERT with ON CONFLICT in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to insert a new user with id 1 and name 'Alice'.

PostgreSQL

INSERT INTO users (id, name) VALUES (1, 'Alice') [1];

Drag options to blanks, or click blank then click option'

AON CONFLICT DO NOTHING

BWHERE id = 1

CON CONFLICT (id) DO NOTHING

DON DUPLICATE KEY UPDATE name = 'Alice'

Attempts:

3 left

💡 Hint

Common Mistakes

Using ON DUPLICATE KEY UPDATE which is MySQL syntax.

Forgetting to specify the conflict column.

The ON CONFLICT (id) DO NOTHING clause tells PostgreSQL to skip the insert if a user with the same id already exists.

2fill in blank

medium

Complete the code to update the user's name to 'Bob' if a conflict on id occurs.

PostgreSQL

INSERT INTO users (id, name) VALUES (2, 'Bob') ON CONFLICT (id) [1];

Drag options to blanks, or click blank then click option'

ADO NOTHING

BDO UPDATE SET name = EXCLUDED.name

CDO UPDATE SET id = EXCLUDED.id

DDO UPDATE SET name = 'Alice'

Attempts:

3 left

💡 Hint

Common Mistakes

Using DO NOTHING when update is needed.

Trying to update the primary key column.

The DO UPDATE SET name = EXCLUDED.name updates the existing row's name to the new value if a conflict on id happens.

3fill in blank

hard

Fix the error in the code to update the user's email on conflict.

PostgreSQL

INSERT INTO users (id, email) VALUES (3, '[e-mail]') ON CONFLICT (id) DO UPDATE SET email = [1];

Drag options to blanks, or click blank then click option'

AEXCLUDED.email

B'[e-mail]'

Cemail

Attempts:

3 left

💡 Hint

Common Mistakes

Using a literal string instead of EXCLUDED.email.

Using the column name without EXCLUDED.

Use EXCLUDED.email to refer to the new email value from the insert statement during the update.

4fill in blank

hard

Fill both blanks to insert a product or update its price on conflict.

PostgreSQL

INSERT INTO products (product_id, price) VALUES (10, 99.99) ON CONFLICT ([1]) DO UPDATE SET [2] = EXCLUDED.price;

Drag options to blanks, or click blank then click option'

Aproduct_id

Bprice

Cid

Dcost

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong column names for conflict or update.

Confusing id with product_id.

The conflict is on product_id, and the price column is updated to the new value.

5fill in blank

hard

Fill all three blanks to insert a record or update both name and email on conflict.

PostgreSQL

INSERT INTO customers (customer_id, name, email) VALUES (5, 'Eve', '[e-mail]') ON CONFLICT ([1]) DO UPDATE SET [2] = EXCLUDED.name, [3] = EXCLUDED.email;

Drag options to blanks, or click blank then click option'

Acustomer_id

Bname

Cemail

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong column names for conflict or update.

Forgetting to update both columns.

The conflict is on customer_id. On conflict, update the name and email columns with the new values.

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