指向重复约束值的Upsert错误(On Conflict Do Update)。[英] Upsert error (On Conflict Do Update) pointing to duplicate constrained values

本文是小编为大家收集整理的关于指向重复约束值的Upsert错误(On Conflict Do Update)。的处理方法,想解了指向重复约束值的Upsert错误(On Conflict Do Update)。的问题怎么解决?指向重复约束值的Upsert错误(On Conflict Do Update)。问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

当我尝试在 FROM 语句中使用多个源时,我在 Postgres 9.5 中遇到了 ON CONFLICT DO UPDATE 问题.

工作代码示例:

    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 语句中添加了一个附加源,以确保只有具有标题、年份和作者的条目被插入到新数据库中.(如果 old."Reference"."ID" 存在于 old."ReferenceAuthor" 为 "ReferenceID",则存在作者.)即使没有附加的 WHERE 语句,查询也是错误的.我在 SELECT 中指定的列只存在于 old."Reference" 中,而不存在于 old."ReferenceAuthor" 中.目前 old."ReferenceAuthor" 和 old."Reference" 没有唯一约束,专着的唯一约束是:

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 命令不能再次影响行提示:确保在同一命令中建议插入的行没有重复的约束值.********** 错误**********

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

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

推荐答案

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

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)
; 

本文地址:https://www.itbaoku.cn/post/1763798.html