SQL
Performing Upsert Operations with INSERT ... ON CONFLICT (PostgreSQL)
Master the SQL `INSERT ... ON CONFLICT DO UPDATE` statement in PostgreSQL for atomic upsert operations, efficiently inserting new rows or updating existing ones based on unique constraints.
INSERT INTO users (id, username, email, last_login)
VALUES (101, 'john_doe', '[email protected]', NOW())
ON CONFLICT (id) DO UPDATE SET
username = EXCLUDED.username,
email = EXCLUDED.email,
last_login = NOW();
How it works: This PostgreSQL-specific snippet shows how to perform an "upsert" operation, meaning "insert if not exists, else update". It attempts to insert a new user record. If a conflict occurs on the `id` unique constraint, it updates the existing record with the new `username`, `email`, and a current `last_login` timestamp. `EXCLUDED` refers to the row that would have been inserted had there been no conflict. This pattern is crucial for managing data integrity and idempotency in web applications.