Re: Is it possible to write a generic UPSERT?

Поиск
Список
Период
Сортировка
От Michael Lewis
Тема Re: Is it possible to write a generic UPSERT?
Дата
Msg-id CAHOFxGqciJKneQJpxhoiW50S9_VM24vcX+bjPz4WD6uC8FsbKw@mail.gmail.com
обсуждение исходный текст
Ответ на Is it possible to write a generic UPSERT?  (Mario Emmenlauer <mario@emmenlauer.de>)
Ответы Re: Is it possible to write a generic UPSERT?  (Andreas Kretschmer <andreas@a-kretschmer.de>)
Re: Is it possible to write a generic UPSERT?  (Mario Emmenlauer <mario@emmenlauer.de>)
Список pgsql-general
Дерево обсуждения
On Thu, Nov 12, 2020 at 6:58 AM Mario Emmenlauer <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 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?).

В списке pgsql-general по дате отправления:

Предыдущее
От: Mario Emmenlauer
Дата:
Сообщение: Re: Is it possible to write a generic UPSERT?
Следующее
От: Bruce Momjian
Дата:
Сообщение: Re: Encryption with customer provided key in a multi tenant Postgres JSONB DB