ON CONFLICT 子句，如何用 DO UPDATE 替换 DO NOTHING? [英] ON CONFLICT clause, how to replace DO NOTHING by DO UPDATE?

问题描述

我的 INSERT 正在使用语法

My INSERT was working with syntax

INSERT INTO t  SELECT * FROM fdw_t
  ON CONFLICT DO NOTHING;

现在我需要通过添加 UPDATE 来更改它，但是保留所有相同的条件，该怎么做?

Now I need to change it by adding UPDATE, but preserving all same conditctions, how to do it?

这是有效的，但它不是所有相同的条件":

This is working, but it is not "all same condictions":

INSERT INTO t
SELECT * FROM fdw_t
ON CONFLICT (id) -- why I need to add this specific and not generic condiction???
DO UPDATE
SET id = EXCLUDED.id, etc1 = EXCLUDED.etc1, etc2 = EXCLUDED.etc2;

我还需要在 UPDATE 中所有可用约束"，而不仅仅是一个约束.关于conflict_target<的指南/em> 说

I need also in UPDATE "all usable constraints", not only one constranct. The Guide about conflict_target says that

必须提供conflict_target

conflict_target must be provided

那么，如何提供所有可用的约束"?

so, how to provide the "all usable constraints"?

推荐答案

我不能说这个限制是因为基本问题还是因为实施考虑.

I cannot say if this limitation is because of fundamental problems or because of implementation considerations.

您将不得不求助于传统技术:编写一个无限循环，交替尝试 INSERT 和 UPDATE操作成功.

You will have to resort to the traditional technique: code an endless loop that alternatively tries to INSERT and UPDATE and break out of the loop as soon as one of the operations succeeds.

这篇关于ON CONFLICT 子句，如何用 DO UPDATE 替换 DO NOTHING?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！