[GENERAL] upsert: is there a shortcut?

Поиск
Список
Период
Сортировка
От Daniele Varrazzo
Тема [GENERAL] upsert: is there a shortcut?
Дата
Msg-id CA+mi_8Zb8QCBT_c2ntzt_VwgCf5xiZOJi6Y_D_YZ0DqhDqS=LQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: [GENERAL] upsert: is there a shortcut?  (Peter Geoghegan <pg@bowt.ie>)
Список pgsql-general
Hello,

it seems not, but I feel like asking.

Is there a way to express a statement like "if you have a conflict on
insert replace all the values in the conflicting record" without
specifying all the fields explicitly? I.e. in a replication system
(where occasional accidents mean replication may restart slightly
before what's already on the target) I generate statements like:

insert into "order_log" ("id","cr_date","order_id","message")
    values (%s, %s, %s, %s)
    on conflict ("id") do update
    set ("cr_date","order_id","message") =
        (excluded."cr_date",excluded."order_id",excluded."message")

Is there a way to avoid replicating the list of fields and use instead
something like (new.*) = (excluded.*) as one could do in a trigger?
(that would also imply an (id = excluded.id but it seems harmless).

It seems to me an use case common enough that some syntactic help...
would help. "do update *"? "do update (target.*) = (excluded.*)"?


-- Daniele


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

Предыдущее
От: vinny
Дата:
Сообщение: Re: [GENERAL] Row based permissions: at DB or at Application level?
Следующее
От: Vincenzo Romano
Дата:
Сообщение: [GENERAL] RETURNS SETOF RECORD with 1 column