How to check how many rows were updated and inserted in a ON DUPLICATE statement?

Question

I have a statement that looks like this:

$count=0;
while($row = pg_fetch_assoc($result)){
$sql=("INSERT INTO joblist (job_no, billed, completed, paid, paid_amount, inv_no, invoice, type, o_submitted, approval_date, gals, jobtype, name, state, region, territory) 
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE
job_no=VALUES(job_no), billed=VALUES(billed),completed=VALUES(completed), paid=VALUES(paid), paid_amount=VALUES(paid_amount), inv_no=VALUES(inv_no), invoice=VALUES(invoice), type=VALUES(type), o_submitted=VALUES(o_submitted), approval_date=VALUES(approval_date), gals=VALUES(gals), jobtype=VALUES(jobtype), name=VALUES(name), state=VALUES(state), region=VALUES(region), territory=VALUES(territory)");
$stmt = $conn->prepare($sql);
$stmt->bind_param("ssssssssssssssss",$job_no, $billed, $completed, $paid, $paid_amount, $inv_no, $invoice, $type, $o_submitted, $approval_date, $gals, $jobtype, $name, $state, $region, $territory);
$stmt->execute();
$count++;
}

The problem is, I cannot decipher between updated rows and inserted rows. Is there a way I can do this? I know i can use the effected rows function, but it reads the same if updated/inserted. Any ideas? thanks!

This SQL syntax won't work with PostgreSQL. If you mean `INSERT ... ON CONFLICT`, last time I looked the number of affected rows was the number of inserted rows. — Laurenz Albe, Sep 17 '20 at 02:34

Jim Jones · Answer 1 · 2020-09-17T10:11:58.027

You're looking for an UPSERT with a RETURNING clause.

Taking into account the following table a records ..

CREATE TEMPORARY TABLE t 
(id INT PRIMARY KEY, name TEXT);

INSERT INTO t VALUES (1,'elgar'),(2,'bach'),(3,'brahms');

.. you can use an UPSERT to catch a primary key conflict and ask the query to return the affected records. You can put this insert inside a CTE and count it with a new query. The following query will insert two already existing primary keys (1 and 2) and a new one (4):

WITH j (affected_rows) AS (
  INSERT INTO t VALUES (1,'edward'),(2,'johann sebastian'),(4,'schubert')
  ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name
  RETURNING *
) SELECT count(affected_rows) FROM j;

  count 
-------
     3

See the results yourself :-)

SELECT * FROM t ORDER BY id;
 id |       name       
----+------------------
  1 | edward
  2 | johann sebastian
  3 | brahms
  4 | telemann

How to check how many rows were updated and inserted in a ON DUPLICATE statement?

1 Answers1