SQL
Upserting Records with PostgreSQL's ON CONFLICT
Efficiently insert new records or update existing ones in PostgreSQL. Utilize INSERT ... ON CONFLICT DO UPDATE to prevent duplicate key errors and maintain data integrity, a must for web apps.
INSERT INTO products (id, name, price)
VALUES (1, 'Widget A', 29.99)
ON CONFLICT (id) DO UPDATE SET
name = EXCLUDED.name,
price = EXCLUDED.price;
How it works: This PostgreSQL query demonstrates an 'upsert' operation, meaning it will either insert a new record or update an existing one if a unique constraint conflict occurs. The `ON CONFLICT (id)` clause specifies the unique constraint (e.g., primary key) to check. If a row with the same `id` already exists, the `DO UPDATE SET` clause is executed, updating the `name` and `price` fields. `EXCLUDED.name` refers to the value that would have been inserted if there was no conflict, ensuring the update uses the new data.