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

medium📝 query result Q13 of 15

PostgreSQL - Set Operations and Advanced Queries

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.

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

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

CThe insert fails with a duplicate key error.

DThe existing row is deleted and a new row inserted.

Step-by-Step Solution

Solution:

Step 1: Understand ON CONFLICT DO UPDATE behavior
The query updates the existing row where username='alice' because of the conflict on username.
Step 2: Calculate updated score
The update sets score = users.score + EXCLUDED.score = 5 + 10 = 15, and returns the updated row.
Final Answer:
The existing row's score is updated to 15 and returned. -> Option B
Quick Check:
ON CONFLICT DO UPDATE adds scores = 15 [OK]

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

Common Mistakes:

Master "Set Operations and Advanced Queries" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Want More Practice?

15+ quiz questions · All difficulty levels · Free