Re: Is it possible to write a generic UPSERT?

On 12.11.20 17:55, Alban Hertroys wrote:
>> On 12 Nov 2020, at 14:58, Mario Emmenlauer <mario(at)emmenlauer(dot)de> wrote:
>
> (…)
>
>> But the statement is slightly complex to type, and I find me and my
>> colleagues often spend more time on this than I would hope. Our two
>> main challenges are:
>> (1) we have to look up the uniqueness constraints on the table, and
>> (2) we have to duplicate the insert statement in the UPDATE section
>> again, because virtually all fields should get overwritten
>> (except for the conflicting ones). On long inserts this can be
>> quite annoying and error-prone.
>>
>> 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
>>
>> In my (naiive) thinking, such a construct would cover 99% of our
>> use cases. Or did other people make very different experiences?
>
> (…)
>
>> Has anybody ever done something like this? Is there an SQL way to
>> achieve this? Or another programmatic way?
>
> We generate the SQL @work based on the definitions in, IIRC, the information_schema. It has tables for both the column lists per table and the primary key definitions.
>
> With that, an SQL statement that returns the required SQL statement is easy to generate, after which you can execute it either from a plpgsql execute statement in a function or in a do-block.

This is actually a very very interesting idea! I did not consider
that we could completely generate the statements based on the actual
table information from the information_schema. I need to give this a
bit more thought but I very much like the idea, thanks for pushing me
in a new direction!

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/