Search Postgresql Archives

Re: Is it possible to write a generic UPSERT?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 12.11.20 18:34, Michael Lewis wrote:
> On Thu, Nov 12, 2020 at 6:58 AM Mario Emmenlauer <mario@xxxxxxxxxxxxx <mailto:mario@xxxxxxxxxxxxx>> 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?).

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/





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux