SQL
Perform Upsert (Insert or Update) with ON CONFLICT
Efficiently insert a new database record or update an existing one if a unique constraint conflict occurs, preventing duplicate entries.
INSERT INTO products (product_id, name, price, stock)
VALUES ('PROD001', 'Laptop Pro', 1200.00, 50)
ON CONFLICT (product_id) DO UPDATE SET
name = EXCLUDED.name,
price = EXCLUDED.price,
stock = products.stock + EXCLUDED.stock; -- Add new stock to existing
How it works: This SQL snippet demonstrates an 'upsert' operation, which means 'insert if not exists, else update'. Using `INSERT ... ON CONFLICT (column_name) DO UPDATE SET ...` (available in PostgreSQL), you can atomically handle cases where you try to insert a row that would violate a unique constraint. If `product_id` 'PROD001' already exists, the `ON CONFLICT` clause triggers an update, setting the name, price, and adding the new `stock` value to the existing one. The `EXCLUDED` keyword refers to the values that would have been inserted had there been no conflict. This is extremely useful for data synchronization and managing unique records without explicit checks.