BrevFeed
Sign in

15. UPSERT and RETURNING

📖 Reading · 5 min
💡 Every code box below is live — edit it and hit Run.

Two features that make writes far more useful in real applications: upsert (insert-or-update in one step) and RETURNING (get data back from a write).

Seeded table for this page — note id is the PRIMARY KEY:

CREATE TABLE product (
  id    INTEGER PRIMARY KEY,
  name  VARCHAR NOT NULL,
  price DECIMAL(6,2) NOT NULL
);
INSERT INTO product (id, name, price) VALUES
  (1, 'Notebook', 4.50),
  (2, 'Pen',      1.25);

The problem upsert solves

You want to insert a row, but it might already exist. A plain INSERT with an existing id fails on the primary key. INSERT ... ON CONFLICT handles the clash for you.

Do nothing on a clash — insert if new, otherwise leave it be:

INSERT INTO product (id, name, price) VALUES (1, 'Notebook', 9.99)
ON CONFLICT (id) DO NOTHING;

SELECT * FROM product ORDER BY id;

Row 1 already exists, so nothing changes.

Update on a clash — the real upsert. excluded refers to the row you tried to insert:

INSERT INTO product (id, name, price) VALUES (1, 'Notebook', 9.99)
ON CONFLICT (id) DO UPDATE SET price = excluded.price;

SELECT * FROM product ORDER BY id;

Now row 1's price becomes 9.99. Insert a brand-new id the same way and it's simply inserted. One statement, both cases handled.

RETURNING: get data back from a write

Normally a write just reports how many rows changed. RETURNING makes it hand back columns from the affected rows — perfect for grabbing a generated id or confirming a new value without a second query:

INSERT INTO product (id, name, price) VALUES (3, 'Backpack', 39.00)
RETURNING id, name;

RETURNING works on UPDATE and DELETE too — e.g. DELETE FROM product WHERE id = 2 RETURNING * gives you back the row you just removed.

Try it

Upsert product id = 2: try to insert (2, 'Pen', 2.00), and on conflict update the price to the new value. Then select all rows ordered by id.

-- upsert id 2 so its price becomes 2.00, then select all ordered by id

INSERT INTO product (id, name, price) VALUES (2, 'Pen', 2.00)
ON CONFLICT (id) DO UPDATE SET price = excluded.price;
SELECT * FROM product ORDER BY id;
← Previous Next →