SQL
Perform Upsert (Insert or Update) in PostgreSQL
Master the upsert operation in PostgreSQL using `INSERT ... ON CONFLICT DO UPDATE` to atomically insert a new record or update an existing one if a unique conflict occurs.
INSERT INTO users (email, username, last_login_at, login_count)
VALUES ('[email protected]', 'testuser', NOW(), 1)
ON CONFLICT (email) DO UPDATE SET
username = EXCLUDED.username,
last_login_at = NOW(),
login_count = users.login_count + 1;
How it works: This SQL snippet demonstrates an "upsert" operation in PostgreSQL, which allows you to atomically insert a row if it doesn't exist or update it if a conflict occurs on a unique constraint (in this case, the `email` column). `EXCLUDED.username` refers to the value that would have been inserted if there was no conflict. This is crucial for managing user data, counters, or synchronization tasks where you want to avoid duplicates and ensure data consistency.