SQL
SQL Upsert Operation using INSERT ... ON CONFLICT (PostgreSQL)
Efficiently handle data insertion or update operations using the SQL UPSERT pattern with `INSERT ... ON CONFLICT` for PostgreSQL, avoiding duplicate entries.
INSERT INTO users (username, email, last_login)
VALUES ('john_doe', '[email protected]', NOW())
ON CONFLICT (username) DO UPDATE SET
email = EXCLUDED.email,
last_login = EXCLUDED.last_login,
updated_at = NOW();
How it works: This snippet demonstrates an "upsert" operation, which attempts to `INSERT` a new row or `UPDATE` an existing one if a conflict occurs. Here, if a `username` ('john_doe') already exists (due to a unique constraint on the `username` column), the `ON CONFLICT (username) DO UPDATE SET` clause is triggered. It updates the `email`, `last_login`, and `updated_at` fields of the existing user with the values provided for the attempted insertion (`EXCLUDED.email`, `EXCLUDED.last_login`). This is highly useful for data synchronization and preventing duplicate records. (Note: Syntax may vary slightly for other databases like MySQL's `ON DUPLICATE KEY UPDATE`).