SQL
Perform an Upsert (Insert or Update) Operation in SQL
Master the SQL UPSERT pattern to either insert a new row or update an existing one when a unique conflict occurs, crucial for data synchronization.
INSERT INTO users (email, username, last_login) VALUES ('[email protected]', 'testuser', NOW())
ON CONFLICT (email) DO UPDATE SET
username = EXCLUDED.username,
last_login = EXCLUDED.last_login
RETURNING id, email, username;
How it works: This PostgreSQL-specific query demonstrates an 'upsert' operation, which attempts to `INSERT` a new row. If a conflict occurs on a unique constraint (in this case, on the `email` column), it instead `UPDATE`s the existing row. The `EXCLUDED` keyword refers to the row values that would have been inserted had there been no conflict. The `RETURNING` clause allows you to get the affected row's data, whether inserted or updated.