sql - 如何在 RETURNING from INSERT ... ON CONFLICT 中包含排除的行

Question

我有这张表(由 Django 生成):

CREATE TABLE feeds_person (
  id serial PRIMARY KEY,
  created timestamp with time zone NOT NULL,
  modified timestamp with time zone NOT NULL,
  name character varying(4000) NOT NULL,
  url character varying(1000) NOT NULL,
  email character varying(254) NOT NULL,
  CONSTRAINT feeds_person_name_ad8c7469_uniq UNIQUE (name, url, email)
);

我正在尝试使用带有 ON CONFLICT 子句的 INSERT 批量插入大量数据。

问题是我需要为 所有 行取回 id，无论它们是否已经存在。

在其他情况下，我会这样做:

INSERT INTO feeds_person (created, modified, name, url, email)
VALUES blah blah blah
ON CONFLICT (name, url, email) DO UPDATE SET url = feeds_person.url
RETURNING id

执行 UPDATE 会导致语句返回该行的 id。除了，它不适用于这张 table 。我认为它不起作用，因为我有多个唯一的字段在一起，而在其他情况下我使用这种方法我只有一个唯一的字段。

尝试通过 Django 的游标运行 SQL 时出现此错误:

django.db.utils.ProgrammingError: ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

如何使用此表进行批量插入并取回插入的和现有的 ID？

Answer 1

你得到的错误:

ON CONFLICT DO UPDATE command cannot affect row a second time

... 表示您正尝试在单个命令中多次插入同一行。换句话说:你在 (name, url, email) 上被骗了在你的VALUES列表。折叠重复项(如果这是一个选项)，错误就会消失。这从每组 dupes 中选择任意行:

INSERT INTO feeds_person (created, modified, name, url, email)
SELECT DISTINCT ON (name, url, email) *
FROM  (
   VALUES
   ('blah', 'blah', 'blah', 'blah', 'blah')
   -- ... more rows
   ) AS v(created, modified, name, url, email)  -- match column list
ON     CONFLICT (name, url, email) DO UPDATE
SET    url = feeds_person.url
RETURNING id;

由于我们使用独立的 VALUES现在，您必须为非默认类型添加显式类型转换。喜欢:

VALUES
    (timestamptz '2016-03-12 02:47:56+01'
   , timestamptz '2016-03-12 02:47:56+01'
   , 'n3', 'u3', 'e3')
   ...

你的 timestamptz列需要显式类型转换，而字符串类型可以使用默认值 text 操作. (您仍然可以立即转换为 varchar(n)。)

如果您想在从每组骗子中选择哪一行有发言权，有一些方法可以做到这一点:

• Select first row in each GROUP BY group?

你是对的，(目前)没有办法使用 excluded RETURNING 中的列条款。我引用 Postgres Wiki :

Note that RETURNING does not make visible the "EXCLUDED.*" aliasfrom the UPDATE (just the generic "TARGET.*" alias is visiblethere). Doing so is thought to create annoying ambiguity for thesimple, common cases [30] for little to no benefit. At somepoint in the future, we may pursue a way of exposing ifRETURNING-projected tuples were inserted and updated, but thisprobably doesn't need to make it into the first committed iteration ofthe feature [31].

但是，您不应更新不应更新的行。空更新几乎与常规更新一样昂贵 - 并且可能会产生意想不到的副作用。您一开始并不严格需要 UPSERT，您的案例看起来更像是“SELECT 或 INSERT”。相关:

• Is SELECT or INSERT in a function prone to race conditions?

一种更简洁的插入一组行的方法是使用数据修改 CTE:

WITH val AS (
   SELECT DISTINCT ON (name, url, email) *
   FROM  (
      VALUES 
      (timestamptz '2016-1-1 0:0+1', timestamptz '2016-1-1 0:0+1', 'n', 'u', 'e')
    , ('2016-03-12 02:47:56+01', '2016-03-12 02:47:56+01', 'n1', 'u3', 'e3')
      -- more (type cast only needed in 1st row)
      ) v(created, modified, name, url, email)
   )
, ins AS (
   INSERT INTO feeds_person (created, modified, name, url, email)
   SELECT created, modified, name, url, email FROM val
   ON     CONFLICT (name, url, email) DO NOTHING
   RETURNING id, name, url, email
   )
SELECT 'inserted' AS how, id FROM ins  -- inserted
UNION  ALL
SELECT 'selected' AS how, f.id         -- not inserted
FROM   val v
JOIN   feeds_person f USING (name, url, email);

增加的复杂性应该为大表付出代价，其中 INSERT是规则并且SELECT异常。

最初，我添加了一个 NOT EXISTS谓词最后 SELECT以防止结果重复。但这是多余的。 单个查询的所有 CTE 都会看到相同的表快照。返回的集合带有 ON CONFLICT (name, url, email) DO NOTHING与 INNER JOIN 之后返回的集合互斥在同一列上。

不幸的是，这也打开了一个竞争条件的小窗口。如果……

• 并发事务插入冲突行
• 还没有 promise
• 但最终会做出 promise

...有些行可能会丢失。

你可能只是 INSERT .. ON CONFLICT DO NOTHING ，后跟一个单独的 SELECT查询所有行 - 在同一个事务中克服这个问题。如果并发事务可以在 INSERT 之间提交对表的写入，这又会打开另一个竞争条件的小窗口和 SELECT (默认 READ COMMITTED isolation level )。可以用 REPEATABLE READ transaction isolation 避免(或更严格)。或者在整个表上使用(可能昂贵甚至 Not Acceptable )写锁。您可以获得所需的任何行为，但可能需要付出代价。

相关:

• How to use RETURNING with ON CONFLICT in PostgreSQL?
• Return rows from INSERT with ON CONFLICT without needing to update