22

I get the canonical example:

 INSERT INTO user_logins (username, logins)
 VALUES ('Naomi',1),('James',1)
 ON CONFLICT (username)
 DO UPDATE SET logins = user_logins.logins + EXCLUDED.logins;

But now I also need to know:

  1. How many rows were inserted
  2. How many rows were updated because existing
  3. How many rows could not be inserted because of constraints
  4. If the constraint is not respected for the last row, will the previous inserted/updated rows be persisted in the DB?
sscarduzio
  • 5,938
  • 5
  • 42
  • 54

1 Answers1

20

I do not know how else you can understand what event occurred. You should look at the value of xmax, if xmax = 0 means there row was inserted, other value xmax there row was update.

I have bad English and I will try to show the example.

create table test3(r1 text unique, r2 text);
\d+ test3
                       Table "public.test3"
 Column | Type | Modifiers | Storage  | Stats target | Description 
--------+------+-----------+----------+--------------+-------------
 r1     | text |           | extended |              | 
 r2     | text |           | extended |              | 
Indexes:
    "test3_r1_key" UNIQUE CONSTRAINT, btree (r1)

INSERT 

INSERT INTO test3 
VALUES('www7','rrr'), ('www8','rrr2') 
ON CONFLICT (r1) DO UPDATE SET r2 = 'QQQQ' RETURNING xmax;
 xmax 
------
    0
    0

If you try to insert a duplicate:

INSERT INTO test3 
VALUES('www7','rrr'), ('www8','rrr2') 
ON CONFLICT (r1) DO UPDATE SET r2 = 'QQQQ' RETURNING xmax;
   xmax    
-----------
 430343538
 430343538
(2 rows)

INSERT 0 2

The result can be processed in such a way:
Inserting 1 new and 1 duplicate rows

WITH t AS  (
  INSERT INTO test3 
  VALUES('www9','rrr'), ('www7','rrr2') 
  ON CONFLICT (r1) DO UPDATE SET r2 = 'QQQQ' RETURNING xmax
) 
SELECT COUNT(*) AS all_rows, 
       SUM(CASE WHEN xmax = 0 THEN 1 ELSE 0 END) AS ins, 
       SUM(CASE WHEN xmax::text::int > 0 THEN 1 ELSE 0 END) AS upd 
FROM t;

all_rows  | ins | upd 
----------+-----+-----
        2 |   1 |   1

see 5.4. System Columns and MVCC

Very interesting how can solve the problem more gracefully

Rhim
  • 644
  • 6
  • 12
  • 1
    What's the significance of the cast `::text::int`? – Nimai Mar 21 '21 at 17:10
  • @Nimai: See https://stackoverflow.com/questions/49597793/what-does-this-do-in-postgresql-xmaxtextint-0. Essentially, `xmax` doesn't have a type that allows normal integer operations. FWIW, I used `xmax != 0` for the second condition; this doesn't need a cast and seems to do the same. – Gerhard Aug 01 '23 at 21:55
  • 1
    @Gerhard Beautifully done, I didn't think of it at that moment. The first thing that came to mind was to cast it to the necessary type. – Rhim Aug 04 '23 at 16:43