如何在从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)
);

我试图INSERT通过ON CONFLICT子句批量插入很多数据。

皱纹是，我需要得到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行的。除此之外，它不适用于此表。我认为这是行不通的，因为我拥有多个唯一的字段，而在其他情况下，我使用此方法时只有一个唯一的字段。

尝试通过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和现有ID？

Answer 1

你得到的错误：

ON CONFLICT DO UPDATE命令不能再次影响行

表示你试图在单个命令中多次向上插入同一行。换句话说：你(name, url, email)的VALUES名单上有骗子。折叠重复项（如果可以的话），它应该可以工作。但是你将不得不决定从每组重复中选择哪一行。

INSERT INTO feeds_person (created, modified, name, url, email)
SELECT DISTINCT ON (name, url, email) *
FROM  (
   VALUES
   ('blah', 'blah', 'blah', 'blah', 'blah')
   -- ... more
   ) 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列需要显式类型转换，而字符串类型可以使用default操作text。（你仍然可以立即投射varchar(n)。）

有一些方法可以确定从每个重复对象中选择哪一行：

在每个GROUP BY组中选择第一行？
没错，（当前）没有办法在子句中获取排除的行RETURNING。我引用Postgres Wiki：

请注意，RETURNING这不会使“ EXCLUDED.*”别名可见UPDATE（只有通用的“ TARGET.*”别名在此处可见）。人们认为这样做会为简单，常见的情况[30]带来烦人的歧义，却几乎没有好处。在将来的某个时候，我们可能会寻求一种方法来公开是否 RETURNING插入和更新了-projected元组，但这可能不需要使它成为该功能的首次提交迭代[31]。

但是，你不应该更新不应更新的行。空更新几乎与常规更新一样昂贵-可能会有意想不到的副作用。你并非一开始就完全需要UPSERT，你的案例看起来更像是“ SELECT或INSERT”。有关：

SELECT或INSERT函数是否易于出现竞争状况？
插入一组行的一种更干净的方法是使用修改数据的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与同一列上的之后返回的集合互斥。

不幸的是，这也为比赛条件打开了一个很小的窗口。如果…

• 并发事务插入冲突的行
• 尚未承诺
• 但最终承诺
  …某些行可能会丢失。
  

你可能只是INSERT .. ON CONFLICT DO NOTHING，紧接着是SELECT对所有行的单独查询-在同一个事务内即可解决此问题。这反过来又打开另一个小窗户的竞争条件，如果并发事务可以承诺之间写入表INSERT和SELECT（在默认的READ COMMITTED隔离级别）。可以通过REPEATABLE READ事务隔离（或更严格）来避免。或者在整个表上使用（可能很昂贵，甚至是不可接受的）写锁。你可以获得所需的任何行为，但可能需要付出一定的代价。