SQL
Perform Upsert (Insert or Update if Exists)
Learn how to execute an "upsert" operation in SQL using PostgreSQL's ON CONFLICT clause, which inserts a new record or updates it if it already exists.
INSERT INTO users (id, username, email)
VALUES (1, 'john_doe', '[email protected]')
ON CONFLICT (id) DO UPDATE SET
username = EXCLUDED.username,
email = EXCLUDED.email;
How it works: This query demonstrates an "upsert" operation specific to PostgreSQL (and SQLite). It attempts to `INSERT` a new user record. If a conflict occurs on the `id` primary key (or unique constraint), the `ON CONFLICT (id) DO UPDATE` clause is triggered, updating the existing row's `username` and `email` with the values provided in the `INSERT` statement (`EXCLUDED` refers to the values that would have been inserted). This is crucial for data synchronization and preventing duplicate entries.