On 12.11.20 18:34, Michael Lewis wrote:
> On Thu, Nov 12, 2020 at 6:58 AM Mario Emmenlauer <mario@emmenlauer.de <mailto:mario@emmenlauer.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
sequencesthat 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
shouldbe 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?).
Actually I'm quite happy you suggest this, because its the one thing
that also came to my mind :) I was not really sure if this is a sane
idea. But it seems this could be the "easiest" way out, because its
short (like "easy to write") and works for all our use cases.
The main potential problem for my use case may be cascading deletes,
that I think we may want to use in the long run.
All the best,
Mario Emmenlauer
--
BioDataAnalysis GmbH, Mario Emmenlauer Tel. Buero: +49-89-74677203
Balanstr. 43 mailto: memmenlauer * biodataanalysis.de
D-81669 München http://www.biodataanalysis.de/