Postgres 9.5+：UPSERT返回更新和插入行数

Question

我理解了典型的例子：

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

但现在我还需要知道：

1. 插入了多少行
2. 由于已有而更新了多少行
3. 由于约束条件无法插入多少行
4. 如果最后一行不遵守约束条件，之前插入/更新的行是否会保留在数据库中？

- sscarduzio

Answer 1

我不知道还有什么方式可以让你理解发生了什么事件。 你应该查看xmax的值，如果xmax = 0表示插入了一行，其他值则表示更新了一行。

我的英语很差，我会尝试举个例子。

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 INTO test3 
VALUES('www7','rrr'), ('www8','rrr2') 
ON CONFLICT (r1) DO UPDATE SET r2 = 'QQQQ' RETURNING xmax;
 xmax 
------
    0
    0

如果您尝试插入重复项：

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行重复的行。

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

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

- Rhim

---

转换 ::text::int 的意义是什么？ - Nimai

@Nimai：参见https://dev59.com/NKrka4cB1Zd3GeqPbEU5。基本上，`xmax`没有一个允许常规整数运算的类型。顺便说一下，我在第二个条件中使用了xmax != 0；这不需要转换并且似乎做了同样的事情。 - Gerhard

@Gerhard 做得很漂亮，我当时没有想到。脑海中首先浮现的是将其转换为所需类型。 - Rhim