Upsert错误（在执行冲突时更新）指向重复的受约束值 [英] Upsert error (On Conflict Do Update) pointing to duplicate constrained values

问题描述

当我尝试在FROM语句中使用多个源时，我对Postgres 9.5中的 ON CONFLICT DO UPDATE 有问题。

I have a problem with ON CONFLICT DO UPDATE in Postgres 9.5 when I try to use more than one source in the FROM statement.

工作代码示例：

    INSERT INTO new.bookmonographs  (citavi_id, abstract, createdon, edition, title, year)
SELECT "ID", "Abstract", "CreatedOn"::timestamp, "Edition", "Title", "Year"
FROM old."Reference"
WHERE old."Reference"."ReferenceType" = 'Book'
    AND old."Reference"."Year" IS NOT NULL
    AND old."Reference"."Title" IS NOT NULL
ON CONFLICT (citavi_id) DO UPDATE 
    SET (abstract, createdon, edition, title, year) = (excluded.abstract, excluded.createdon, excluded.edition, excluded.title, excluded.year)
; 

故障代码：

    INSERT INTO new.bookmonographs  (citavi_id, abstract, createdon, edition, title, year)
SELECT "ID", "Abstract", "CreatedOn"::timestamp, "Edition", "Title", "Year"
FROM old."Reference", old."ReferenceAuthor"
WHERE old."Reference"."ReferenceType" = 'Book'
    AND old."Reference"."Year" IS NOT NULL
    AND old."Reference"."Title" IS NOT NULL
    AND old."ReferenceAuthor"."ReferenceID" = old."Reference"."ID"
    --Year, Title and Author must be present in the data, otherwise the entry is deemed useless, hence won't be included
ON CONFLICT (citavi_id) DO UPDATE 
    SET (abstract, createdon, edition, title, year) = (excluded.abstract, excluded.createdon, excluded.edition, excluded.title, excluded.year)
; 

我在FROM语句和另一个WHERE语句中添加了另一个源，以确保仅包含标题，年份和作者将被插入到新数据库中。 （如果旧的。 Reference。 ID以旧的形式存在。 ReferenceAuthor为 ReferenceID，则存在一个作者。）即使没有附加的WHERE语句，查询也是错误的。我在SELECT中指定的列仅出现在 old。 Reference 中，而不出现在 old。 ReferenceAuthor 中。
当前为 old。 ReferenceAuthor 和 old。 Reference 没有唯一约束， Bookmonograph的唯一约束是：

I added an additional source in the FROM statement and one more WHERE statement to make sure only entries that have a title, year and author are inserted into the new database. (If old."Reference"."ID" exists in old."ReferenceAuthor" as "ReferenceID", then an author exists.) Even without the additional WHERE statement the query is faulty. The columns I specified in SELECT are only present in old."Reference", not in old."ReferenceAuthor". Currently old."ReferenceAuthor" and old."Reference" don't have a UNIQUE CONSTRAINT,the uniqe constraints for bookmonographs are:

CONSTRAINT bookmonographs_pk PRIMARY KEY (bookmonographsid),
CONSTRAINT bookmonographs_bookseries FOREIGN KEY (bookseriesid)
      REFERENCES new.bookseries (bookseriesid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT bookmonographs_citaviid_unique UNIQUE (citavi_id)

错误PSQL引发：

错误：ON CONFLICT DO UPDATE命令不能第二次影响行
提示：确保在同一命令中建议不要插入的行具有重复的约束值。
**********错误**********

ERROR: 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. ********** Error **********

错误：ON CONFLICT DO UPDATE命令不能影响行第二次
SQL状态：21000
提示：确保在同一命令中建议插入的行没有重复的约束值。

ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time SQL state: 21000 Hint: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

我不知道出了什么问题，也不知道为什么提示指向重复的约束值。

I don't know what's wrong, or why the hint points to a duplicated constrained value.

推荐答案

该问题是由以下事实引起的：显然，某些条目具有多个作者。因此，您编写的选择查询中的内部联接将为同一条目返回多个行，而 INSERT ... ON CONFLICT 不喜欢这样。由于仅使用 ReferenceAuthor 表进行过滤，因此您可以简单地重写查询，以便它使用该表仅过滤没有任何作者的条目，方法是执行存在在相关子查询上。方法如下：

The problem is caused by the fact that apparently some entries have multiple authors. So the inner join in the select query that you wrote will return multiple rows for the same entry and INSERT ... ON CONFLICT doesn't like that. Since you only use the ReferenceAuthor table for filtering, you can simply rewrite the query so that it uses that table to only filter entries that don't have any author by doing an exists on a correlated subquery. Here's how:

INSERT INTO new.bookmonographs  (citavi_id, abstract, createdon, edition, title, year)
SELECT "ID", "Abstract", "CreatedOn"::timestamp, "Edition", "Title", "Year"
FROM old."Reference"
WHERE old."Reference"."ReferenceType" = 'Book'
    AND old."Reference"."Year" IS NOT NULL
    AND old."Reference"."Title" IS NOT NULL
    AND exists(SELECT FROM old."ReferenceAuthor" WHERE old."ReferenceAuthor"."ReferenceID" = old."Reference"."ID")
    --Year, Title and Author must be present in the data, otherwise the entry is deemed useless, hence won't be included
ON CONFLICT (citavi_id) DO UPDATE 
    SET (abstract, createdon, edition, title, year) = (excluded.abstract, excluded.createdon, excluded.edition, excluded.title, excluded.year)
; 

这篇关于Upsert错误（在执行冲突时更新）指向重复的受约束值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！