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.