Dear all,
I hope this request is not too naiive. Please ignore if its not
matching this lists focus, or if it just shows my lack of SQL
knowledge. Any pointer to documentation would be appreciated!
My question is about the "ON CONFLICT UPDATE" clause. We often have
one dedicated condition that we want to achieve: Insert an item if
it does not exist, or update all given fields if it does exist.
Your excellent documentation makes it easy to implement this in SQL.
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?
Now the question: I fail to generically implement this. I do not
necessarily mean that this has to be supported by PostgreSQL. Any
solution would work for us, be it in SQL, PostgreSQL or C/C++. For
example an acceptable solution would be a C/C++ wrapper method that
generates the statement, given the corresponding INSERT statement and
the types and values of all arguments. The wrapper would also have
access to the table definitions because we create all tables in code.
We currently do not have concurrency concerns so a more complex
solution using multiple statements would be fine.
Has anybody ever done something like this? Is there an SQL way to
achieve this? Or another programmatic way?
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/
