Postgres 9.5+：UPSERT返回更新和插入的行数 [英] Postgres 9.5+: UPSERT to return the count of updated and inserted rows

问题描述

我得到了一个典型的例子：

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. 插入了多少行


2. 由于已存在而更新了多少行


3. 无法插入多少行由于限制因素


4. 如果最后一行没有遵守该约束条件，那么先前插入/更新的行是否会保留在数据库中？

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?

推荐答案

我不知道您还能怎么理解发生了什么事件。
您应该查看xmax的值，如果xmax = 0表示已插入行，则其他值xmax那里行已更新。

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.

我的英语不好

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

可以用以下方式处理结果：

插入1个新值和1个重复的行

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

请参见 5.4。系统列和 MVCC

非常有趣的是如何更优雅地解决问题

这篇关于Postgres 9.5+：UPSERT返回更新和插入的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！