Re: Is it possible to write a generic UPSERT?

> 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.

We do this in plpgsql, but that’s mostly because this code is part of our ETL process and it has to perform some other logic on the same data anyway. I could look up our code for you tomorrow, but that looks to be a busy day, so I can’t promise.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.