Re: Is it possible to write a generic UPSERT?

On Thu, Nov 12, 2020 at 6:58 AM Mario Emmenlauer <mario(at)emmenlauer(dot)de>
wrote:

> I can see how "ON CONFLICT" is very powerful. But that power seems
> often a burden for us. We would prefer something that is less manual
> effort for the specific use case. Basically, we would like:
> INSERT if not exist, and
> UPDATE _all_ non-conflicting fields in case of _any_ conflict
>

If you do not have significant ratio of HOT (heap only tuple) updates that
you want to preserve and you don't have sequences that are GENERATED AS
ALWAYS (rather than BY DEFAULT), you could consider just doing a DELETE
where the keys exist, then insert all the rows. It should be trivial to
figure out the primary key or other unique index to match on.

MERGE command is implemented for this use case in some DBMS, but not
Postgres (yet?).