SQL
Perform UPSERT (Insert or Update) Operation
Learn to efficiently insert new records or update existing ones based on a unique key in PostgreSQL, preventing duplicates and streamlining data management (UPSERT).
INSERT INTO products (sku, name, price)
VALUES ('P101', 'Widget A', 29.99)
ON CONFLICT (sku) DO UPDATE SET
name = EXCLUDED.name,
price = EXCLUDED.price,
updated_at = NOW();
How it works: This snippet demonstrates an 'upsert' operation in PostgreSQL, which either inserts a new row or updates an existing one if there's a conflict on a unique constraint (in this case, the 'sku' column). If a product with 'P101' already exists, its 'name' and 'price' will be updated to the new values, and the 'updated_at' timestamp will be set. `EXCLUDED` refers to the values that would have been inserted had there been no conflict.