INSERT … ON CONFLICT said 12 rows. Five of them were overwrites.

The short answer

For INSERT … ON CONFLICT DO UPDATE, PostgreSQL’s command tag is INSERT 0 count, where count is the number of rows inserted or updated.¹ It doesn’t tell you the split. To get it, add RETURNING with the old row: on PostgreSQL 18, old.<column> is NULL for rows that were inserted and holds the previous values for rows that were updated.¹ On PostgreSQL 17 and later, a MERGE with RETURNING merge_action() labels each row INSERT or UPDATE.²

In our run of a 12-row upsert where 5 keys already existed, the tag said INSERT 0 12; RETURNING showed 7 inserted and 5 updated, with the old and new prices of the 5.³

The command tag counts both

This is scenario 12 of our benchmark. An agent adds a supplier’s 12 items to a catalogue of 100 products with an upsert keyed on sku. Five of the SKUs are already in the catalogue:

Code A database command in SQL. The text around it says what it does.

INSERT INTO products (sku, name, price_cents) VALUES
  ('P096', 'Supplier item 96', 596),
  -- … eleven more, P097 to P107
  ('P107', 'Supplier item 107', 607)
ON CONFLICT (sku) DO UPDATE
  SET name = EXCLUDED.name, price_cents = EXCLUDED.price_cents;

Code What the screen or file shows, copied as is.

INSERT 0 12

The agent reports 12 new products. Postgres inserted 7 and overwrote the names and prices of 5.⁴ Nothing in INSERT 0 12 is wrong; it’s documented to count both.¹ The mistake is reading it as “12 inserted”.

For comparison, the same statement with ON CONFLICT DO NOTHING answered INSERT 0 7, because skipped rows aren’t counted.³ So the same input gives 12 or 7 depending on the conflict clause, and neither number tells you how many rows existed before.

Why agents get this wrong

The command tag is the one piece of feedback almost every database tool passes back to the model. It’s short, it’s always there, and it looks like an answer. “INSERT 0 12” reads naturally as “twelve rows were inserted”, and a model summarising its work will say exactly that. Nothing in the tag hints that some of those rows existed before.

People make the same mistake. The difference is that an agent may do it hundreds of times a day, and its summary becomes the record a person reads later. If the tool returns only the tag, the agent’s report can’t be better than the tag. The fix is in the tool, not the prompt.

On PostgreSQL 16 and older

Without RETURNING old or MERGE … RETURNING, there’s no documented way to get the split from the upsert statement itself. The plain option is two steps in one transaction: first select which of the incoming keys already exist, then run the upsert. The first query tells you how many will be updates; the upsert’s count minus that is the inserts. It costs a round trip and is only exact if no other session inserts the same keys in between, so run it at a strict isolation level or lock the keys. If you can, upgrade: the one-statement versions above are simpler and race-free.

Count them with RETURNING old

PostgreSQL 18 added OLD and NEW to RETURNING for INSERT, UPDATE, DELETE and MERGE.² For an upsert, the old row is NULL for a row that was inserted, and holds the previous values for one that conflicted and was updated.¹

Code A database command in SQL. The text around it says what it does.

INSERT INTO products (sku, name, price_cents) VALUES …
ON CONFLICT (sku) DO UPDATE
  SET name = EXCLUDED.name, price_cents = EXCLUDED.price_cents
RETURNING sku, (old.sku IS NULL) AS inserted,
          old.price_cents AS old_price, new.price_cents AS new_price;

What we got back, shortened:

Code What the screen or file shows, copied as is.

 sku  | inserted | old_price | new_price
------+----------+-----------+-----------
 P096 | f        |      1096 |       596
 P097 | f        |      1097 |       597
 …
 P100 | f        |      1100 |       600
 P101 | t        |           |       601
 …
 P107 | t        |           |       607
(12 rows)

For a summary an agent can report, wrap it in a CTE:

Code A database command in SQL. The text around it says what it does.

WITH up AS (
  INSERT INTO products (sku, name, price_cents) VALUES …
  ON CONFLICT (sku) DO UPDATE
    SET name = EXCLUDED.name, price_cents = EXCLUDED.price_cents
  RETURNING old.sku IS NULL AS inserted
)
SELECT count(*) FILTER (WHERE inserted)     AS inserted,
       count(*) FILTER (WHERE NOT inserted) AS updated
FROM up;

Code What the screen or file shows, copied as is.

 inserted | updated
----------+---------
        7 |       5

Use a column that can’t be NULL in a real row, such as the primary key, for the IS NULL test. Otherwise a stored NULL looks like an insert.

Or use MERGE

If you’re on PostgreSQL 17, or prefer explicit branches, MERGE says which action it took for each row. Since version 17 it supports RETURNING, and merge_action() reports the command that produced the row.⁵

Code A database command in SQL. The text around it says what it does.

WITH m AS (
  MERGE INTO products p
  USING (SELECT 'P' || lpad(g::text, 3, '0') AS sku,
                'Supplier item ' || g AS name, 500 + g AS price_cents
         FROM generate_series(96, 107) g) s
  ON p.sku = s.sku
  WHEN MATCHED THEN UPDATE SET name = s.name, price_cents = s.price_cents
  WHEN NOT MATCHED THEN INSERT (sku, name, price_cents)
                        VALUES (s.sku, s.name, s.price_cents)
  RETURNING merge_action() AS action
)
SELECT action, count(*) FROM m GROUP BY action;

Code What the screen or file shows, copied as is.

 action | count
--------+-------
 INSERT |     7
 UPDATE |     5

Our run matched the upsert exactly.³ One difference to know: the MERGE docs point to INSERT … ON CONFLICT as the alternative when a concurrent insert of the same key may happen, because it can run an update in that case; the two statements aren’t interchangeable.⁶ For keys several writers hit at once, prefer ON CONFLICT.

When you didn’t mean to overwrite

Sometimes the overwrite is the bug. The agent was asked to add new products, and an upsert silently changed five existing ones. Three ways to make that impossible or visible:

1. Use DO NOTHING when the task is “add new”. Then existing rows are untouched, and RETURNING lists only the rows that were inserted, so you can compare its count with what you sent.¹
2. Put a condition on the update. ON CONFLICT … DO UPDATE … WHERE products.name = EXCLUDED.name updates only when a rule holds. A row locked but not updated because the WHERE failed isn’t returned.¹
3. Report the split, always. Make the agent’s tool return inserted and updated counts, not the command tag. An agent can only report what its tool tells it.

Where our check says it can’t tell

We publish this scenario because our own check doesn’t catch it. Deedglass can’t work out which rows an upsert may change, so it says it can’t check, and it doesn’t report the 5 overwrites.⁴ That’s honest, and a person should look, but it isn’t a catch by our benchmark’s rules. The change log has the 5 updates; the fix above gives you them without any extra tool.

A checklist for agent upserts

1. Is the task “add” or “add or update”? Use DO NOTHING for add.
2. Does the tool return the split? Use RETURNING old or merge_action().
3. Does the agent’s report come from the split, not the tag?
4. Is the conflict target the key you think it is?
5. Do you keep the old values of updated rows somewhere, so an unwanted overwrite can be put back? RETURNING old.* gives them to you at no extra cost.

For more on what PostgreSQL 18’s RETURNING can do, see RETURNING OLD and NEW. For the opposite mistake, a statement that changed more rows than it said, see 1 row vs 301. To try scenarios like this on a real Postgres in your browser, open the playground; to grade what an agent can reach on your machine, run npx deedglass scan (the grade, explained).

Written with AI assistance and checked against our claims register.