SQL
Perform Upsert Operations (Insert or Update) in SQL
Learn to implement atomic upsert operations in SQL, allowing you to insert a row if it doesn't exist or update it if it does, preventing duplicate entries and simplifying data synchronization.
INSERT INTO users (email, username, last_login_at)
VALUES ('[email protected]', 'newuser_name', NOW())
ON CONFLICT (email) DO UPDATE SET
username = EXCLUDED.username,
last_login_at = EXCLUDED.last_login_at;
How it works: This PostgreSQL-specific SQL snippet demonstrates an 'upsert' operation, which means 'insert if not exists, otherwise update'. It attempts to insert a new user record. If a conflict occurs on the `email` column (which should have a unique constraint), the `ON CONFLICT (email) DO UPDATE` clause is triggered. It then updates the `username` and `last_login_at` of the existing record using values from the `EXCLUDED` row (the row that was attempted to be inserted). This prevents duplicate entries and ensures data integrity.