如何在与外部表上的主键冲突时使用

Question

我基本上是试图将数据从一个服务器上的表复制到另一个服务器上。

我在服务器上有两个相同的数据库。我创建了一个名为opentickets_aux1的外表来表示主服务器上辅助服务器上的open票证表。两者都有一个事件号主键。我可以很好地访问外表中的数据，但是当我尝试以下SQL时，我得到了“错误：没有与ON CONFLICT规范匹配的唯一或排除约束”

INSERT INTO opentickets_aux1 (SELECT * FROM opentickets)
ON CONFLICT (incidentnumber)
DO
 UPDATE SET 
 status = EXCLUDED.status,
 lastmodifieddate = EXCLUDED.lastmodifieddate

如果主键存在，我想更新几列。我将此语句用于其他查询，当它是一个本地表时，它们就会工作。有什么想法吗？

Answer 1

外部表不能有主键约束，因为PostgreSQL无法强制执行其完整性。因此，不能使用INSERT。。。与外部表发生冲突。

您的想法也不会处理在外部服务器上删除的行，但这可能是故意的。

如果需要外部表的本地副本，最简单的方法是在外部表上创建物化视图。

如果这不是你的愿望（也许是因为你不想复制删除），你必须使用这样的语句

INSERT INTO localtable
SELECT * FROM foreigntable f
WHERE NOT EXISTS
         (SELECT 1 FROM localtable l
          WHERE f.id = l.id);

UPDATE localtable l
SET /* all columns from f */
FROM foreigntable f
WHERE f.id = l.id
  AND (f.*) <> (l.*);