Exists? Update. New? Insert.
Syncing a price list, counting page views, saving settings — "insert, or update if it's already there" is everywhere. Checking first then deciding is slow and races with other requests. Upserts do it atomically.
INSERT … ON CONFLICT
CREATE TABLE stock (dish text PRIMARY KEY, qty int NOT NULL);
INSERT INTO stock VALUES ('Momo', 50), ('Tea', 100);
INSERT INTO stock (dish, qty) VALUES ('Momo', 20), ('Lassi', 30)
ON CONFLICT (dish) DO UPDATE SET qty = stock.qty + excluded.qty
RETURNING *;
INSERT INTO stock (dish, qty) VALUES ('Tea', 999)
ON CONFLICT (dish) DO NOTHING;
SELECT * FROM stock ORDER BY dish;ON CONFLICT needs a unique constraint or primary key on the conflict columns.