SQL
Perform UPSERT (Conditional Insert/Update)
Master how to insert a new row or update an existing one if a unique conflict occurs, preventing duplicate data and streamlining database operations.
INSERT INTO user_preferences (user_id, setting_key, setting_value)
VALUES (101, 'theme', 'dark')
ON CONFLICT (user_id, setting_key) DO UPDATE SET setting_value = EXCLUDED.setting_value, updated_at = NOW();
How it works: This query demonstrates an 'UPSERT' operation, which either inserts a new row or updates an existing one if a conflict on a unique constraint occurs. Specific to PostgreSQL, `ON CONFLICT (user_id, setting_key)` identifies the unique constraint. If a conflict is detected, `DO UPDATE SET` modifies the existing row with the new values provided by `EXCLUDED.setting_value` and updates the timestamp, otherwise, a new row is inserted.