Q45: How do UPSERT operations work in SQL (MERGE vs ON CONFLICT)?
An UPSERT inserts a new record or updates the existing row if a unique constraint conflict occurs.
- PostgreSQL / SQLite: Uses
INSERT INTO ... ON CONFLICT (key) DO UPDATE. - ANSI Standard / SQL Server / Oracle: Uses
MERGE INTO target USING source ON match_condition.
PostgreSQL UPSERT Syntax Example
INSERT INTO user_scores (user_id, total_score, updated_at)
VALUES (101, 250, CURRENT_TIMESTAMP)
ON CONFLICT (user_id)
DO UPDATE SET
total_score = user_scores.total_score + EXCLUDED.total_score,
updated_at = EXCLUDED.updated_at;💡 If user_id 101 already exists, execution updates the existing row instead of throwing a primary key violation error.
Key Takeaways
- UPSERT operations handle unique key conflict conditions cleanly during concurrent writes.
- PostgreSQL uses
ON CONFLICT; ANSI SQL and SQL Server useMERGE.