Beginner LevelPostgreSQL
How does UPSERT work in PostgreSQL using INSERT ... ON CONFLICT DO UPDATE?
❓ Interview Prompt: Explain declarative UPSERT syntax (`ON CONFLICT (target) DO UPDATE SET ...`), `EXCLUDED` pseudo-table, and `DO NOTHING`.
PostgreSQL provides declarative atomic UPSERT functionality via 'ON CONFLICT':
Key Components:
•
Conflict Target: Specifies unique index column ('ON CONFLICT (email)').
•
'EXCLUDED' Table: A special pseudo-table representing the row values proposed for insertion.
Code Implementation Example
sql
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (101, 1, NOW())
ON CONFLICT (user_id)
DO UPDATE SET
login_count = user_stats.login_count + EXCLUDED.login_count,
last_login = EXCLUDED.last_login;💡Atomic UPSERT query using ON CONFLICT DO UPDATE and EXCLUDED pseudo-table.
🎯 Interview Key Takeaway
Use ON CONFLICT DO UPDATE with the EXCLUDED table to perform atomic upsert operations safely.
#UPSERT#ON CONFLICT#EXCLUDED#Beginner
Back to all PostgreSQL Questions→