SQL
Implementing UPSERT Logic (Insert or Update) in SQL
Perform an atomic insert or update operation in your database, preventing duplicate records and ensuring data consistency with UPSERT logic.
INSERT INTO
users (email, username, last_login)
VALUES
('[email protected]', 'NewUser123', NOW())
ON CONFLICT (email)
DO UPDATE SET
username = EXCLUDED.username,
last_login = EXCLUDED.last_login,
updated_at = NOW();
How it works: The 'UPSERT' operation is a critical pattern for preventing duplicate data and ensuring data consistency. This PostgreSQL-specific snippet uses `ON CONFLICT (email) DO UPDATE SET` to achieve this. If a user with the specified `email` already exists, their `username` and `last_login` (and `updated_at`) are updated; otherwise, a new user record is inserted. This atomic operation simplifies application logic by handling both insertion and updating scenarios seamlessly within a single query.