Basic CRUD OperationsBeginner
1Question 1 of 1
sqlHow does the UPSERT feature work in PostgreSQL using ON CONFLICT?
Explanation & Concept
PostgreSQL provides the `ON CONFLICT` clause on `INSERT` statements to perform atomic 'upserts' (insert if absent, update if present) without application-level race conditions.
You specify the target conflict constraint (usually a column with a UNIQUE index or primary key), and define either `DO NOTHING` or `DO UPDATE SET`.
Code Example
sql
INSERT INTO user_preferences (user_id, theme, notifications_enabled, updated_at)
VALUES (42, 'dark', true, NOW())
ON CONFLICT (user_id)
DO UPDATE SET
theme = EXCLUDED.theme,
notifications_enabled = EXCLUDED.notifications_enabled,
updated_at = NOW();How It Works & Key Takeaways
The special `EXCLUDED` table holds the proposed values from the failed INSERT. If user 42 already exists, PostgreSQL automatically updates the existing row with the incoming values.
Mock Test · 1 Questions · Optional
Ready for Transactions & Isolation Levels?
Take a quick test to evaluate yourself, or move straight to the next module.
Beginner1 questions