Postgres 9.5+: UPSERT to return the count of updated and inserted rows

Question

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:

How many rows were inserted
How many rows were updated because existing
How many rows could not be inserted because of constraints
If the constraint is not respected for the last row, will the previous inserted/updated rows be persisted in the DB?

Rhim · Accepted Answer · 2016-08-11 07:50:18Z

22

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

edited Aug 11, 2016 at 7:50

answered Aug 9, 2016 at 19:09

Rhim

6747 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nimai Over a year ago

What's the significance of the cast ::text::int?

Gerhard Over a year ago

@Nimai: See stackoverflow.com/questions/49597793/…. 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.

Rhim Over a year ago

@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.

Collectives™ on Stack Overflow

Postgres 9.5+: UPSERT to return the count of updated and inserted rows

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related