ON CONFLICT clause, how to replace DO NOTHING by DO UPDATE?

user_287948 0
Viewed 397

My INSERT was working with syntax

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

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;

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

conflict_target must be provided

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

upsert postgresql
1 Answers

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

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.

user_64643080
Related