0
0
PostgreSQLquery~20 mins

INSERT ON CONFLICT (upsert) in PostgreSQL - Practice Problems & Coding Challenges

Choose your learning style9 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Challenge - 5 Problems
🎖️
PostgreSQL UPSERT Master
Get all challenges correct to earn this badge!
Test your skills under time pressure!
❓ query_result
intermediate
2:00remaining
What is the output of this UPSERT query?

Given the table users(id INT PRIMARY KEY, name TEXT, age INT) with initial data:

id | name  | age
---+-------+----
1  | Alice | 30
2  | Bob   | 25

What will be the content of the table after running this query?

INSERT INTO users (id, name, age) VALUES (2, 'Bobby', 26)
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, age = EXCLUDED.age;
PostgreSQL
INSERT INTO users (id, name, age) VALUES (2, 'Bobby', 26)
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, age = EXCLUDED.age;
A
id | name  | age
---+-------+----
1  | Alice | 30
2  | Bob   | 25
B
id | name  | age
---+-------+----
1  | Alice | 26
2  | Bobby | 26
C
id | name  | age
---+-------+----
1  | Alice | 30
2  | Bobby | 26
D
id | name  | age
---+-------+----
1  | Alice | 30
2  | Bobby | 25
Attempts:
2 left
💡 Hint

Remember that ON CONFLICT (id) DO UPDATE updates the existing row with the new values.

❓ query_result
intermediate
2:00remaining
What happens when ON CONFLICT DO NOTHING is used?

Given the table products(id INT PRIMARY KEY, name TEXT, price NUMERIC) with data:

id | name     | price
---+----------+-------
1  | Chair    | 49.99
2  | Table    | 89.99

What will be the table content after running this query?

INSERT INTO products (id, name, price) VALUES (2, 'Desk', 99.99)
ON CONFLICT (id) DO NOTHING;
PostgreSQL
INSERT INTO products (id, name, price) VALUES (2, 'Desk', 99.99)
ON CONFLICT (id) DO NOTHING;
A
id | name   | price
---+--------+-------
1  | Chair  | 49.99
2  | Table  | 89.99
B
id | name  | price
---+-------+-------
1  | Chair | 49.99
2  | Desk  | 99.99
C
id | name   | price
---+--------+-------
1  | Chair  | 49.99
2  | Desk   | 89.99
D
id | name   | price
---+--------+-------
1  | Chair  | 49.99
2  | Table  | 99.99
Attempts:
2 left
💡 Hint

DO NOTHING means the insert is skipped if there is a conflict.

📝 Syntax
advanced
2:00remaining
Which option is the correct syntax for UPSERT with partial update?

Which of the following queries correctly updates only the price column on conflict for the items table?

A
INSERT INTO items (id, name, price) VALUES (1, 'Pen', 1.5)
ON CONFLICT (id) DO SET price = EXCLUDED.price;
B
INSERT INTO items (id, name, price) VALUES (1, 'Pen', 1.5)
ON CONFLICT (id) DO UPDATE price = EXCLUDED.price;
C
INSERT INTO items (id, name, price) VALUES (1, 'Pen', 1.5)
ON CONFLICT (id) UPDATE SET price = EXCLUDED.price;
D
INSERT INTO items (id, name, price) VALUES (1, 'Pen', 1.5)
ON CONFLICT (id) DO UPDATE SET price = EXCLUDED.price;
Attempts:
2 left
💡 Hint

The DO UPDATE SET clause is required after ON CONFLICT.

🔧 Debug
advanced
2:00remaining
Why does this UPSERT query fail with a syntax error?

Consider this query:

INSERT INTO orders (order_id, product, quantity) VALUES (10, 'Book', 3)
ON CONFLICT (order_id) DO UPDATE quantity = EXCLUDED.quantity;

Why does it cause a syntax error?

PostgreSQL
INSERT INTO orders (order_id, product, quantity) VALUES (10, 'Book', 3)
ON CONFLICT (order_id) DO UPDATE quantity = EXCLUDED.quantity;
AMissing the SET keyword after DO UPDATE
BMissing parentheses around conflict target
CVALUES clause is not allowed with ON CONFLICT
DEXCLUDED keyword is invalid here
Attempts:
2 left
💡 Hint

Check the syntax after DO UPDATE.

🧠 Conceptual
expert
2:00remaining
Which statement about INSERT ON CONFLICT is true?

Choose the correct statement about INSERT ON CONFLICT in PostgreSQL.

AON CONFLICT DO UPDATE can reference the existing row using the EXCLUDED keyword
BON CONFLICT requires specifying the conflict target columns or constraint name
CON CONFLICT DO NOTHING will raise an error if a conflict occurs
DON CONFLICT can only handle conflicts on primary keys, not unique constraints
Attempts:
2 left
💡 Hint

Think about what you must specify to use ON CONFLICT.