SQL
Perform Upsert Operations with INSERT ON CONFLICT (PostgreSQL)
Discover how to perform an 'upsert' operation in PostgreSQL, either inserting a new row or updating an existing one if a unique constraint is violated, for seamless data management.
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;
-- Note: For MySQL, a similar operation uses `ON DUPLICATE KEY UPDATE`.
How it works: An upsert operation attempts to insert a new row; if a row with a conflicting unique key (like `email` in this PostgreSQL example) already exists, it instead updates the existing row with the new values. This is incredibly useful for maintaining unique records, such as user profiles or product inventories, without needing to perform a separate `SELECT` query to check for existence first.