INSERT ON CONFLICT (upsert) 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'

AWHERE id = 1

BON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name

CON DUPLICATE KEY UPDATE name = 'Alice'

DON CONFLICT DO NOTHING

Attempts:

3 left

💡 Hint

Common Mistakes

Using MySQL syntax like ON DUPLICATE KEY UPDATE in PostgreSQL.

Forgetting the ON CONFLICT clause and causing errors on duplicates.

The ON CONFLICT DO NOTHING clause tells PostgreSQL to skip the insert if a conflict occurs on any unique constraint.

2fill in blank

medium

Complete the code to update the user's name to 'Bob' if the id conflicts.

PostgreSQL

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

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

ADO UPDATE SET name = EXCLUDED.name

BDO REPLACE

CDO NOTHING

DDO UPDATE SET name = 'Bob'

Attempts:

3 left

💡 Hint

Common Mistakes

Using DO NOTHING when an update is needed.

Trying to use DO REPLACE which is not valid in PostgreSQL.

The DO UPDATE SET name = EXCLUDED.name clause updates the existing row's name to the new value on conflict.

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 (2, '[e-mail]') ON CONFLICT (id) DO UPDATE SET email = [1];

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

AEXCLUDED.email

Bemail

C'[e-mail]'

Dusers.email

Attempts:

3 left

💡 Hint

Common Mistakes

Using the column name without EXCLUDED, which refers to the old value.

Using a string literal instead of the new value.

Use EXCLUDED.email to refer to the new value being inserted during the conflict update.

4fill in blank

hard

Fill both blanks to insert or update the user's name and email on conflict.

PostgreSQL

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

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

AEXCLUDED.name

B'Carol'

CEXCLUDED.email

Dusers.email

Attempts:

3 left

💡 Hint

Common Mistakes

Using string literals instead of EXCLUDED values.

Using table.column syntax which refers to old values.

Use EXCLUDED.name and EXCLUDED.email to update the existing row with the new values on conflict.

5fill in blank

hard

Fill all three blanks to insert or update the user's name, email, and age on conflict.

PostgreSQL

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

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

AEXCLUDED.name

BEXCLUDED.email

CEXCLUDED.age

D'Dave'

Attempts:

3 left

💡 Hint

Common Mistakes

Using string literals instead of EXCLUDED values.

Forgetting to update all columns needed.

Use EXCLUDED.name, EXCLUDED.email, and EXCLUDED.age to update all columns with the new values on conflict.