ON CONFLICT 子句，如何用 DO UPDATE 替换 DO NOTHING?

本文介绍了ON CONFLICT 子句，如何用 DO UPDATE 替换 DO NOTHING?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的 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 说

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.