Postgres Upsert - Software Blog

Postgres UPSERT operation

Source: here

Excerpt:

CREATE TABLE inventory(
   id INT PRIMARY KEY,
   name VARCHAR(255) NOT NULL,
   price DECIMAL(10,2) NOT NULL,
   quantity INT NOT NULL
);

INSERT INTO inventory(id, name, price, quantity)
VALUES
	(1, 'A', 15.99, 100),
	(2, 'B', 25.49, 50),
	(3, 'C', 19.95, 75)
RETURNING *;

Here, EXCLUDED means "use the new value"

INSERT INTO inventory (id, name, price, quantity)
VALUES (1, 'A', 16.99, 120)
ON CONFLICT(id) 
DO UPDATE SET
  price = EXCLUDED.price,
  quantity = EXCLUDED.quantity;