From: | Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | upsert: is there a shortcut? |
Date: | 2017-07-27 11:20:51 |
Message-ID: | CA+mi_8Zb8QCBT_c2ntzt_VwgCf5xiZOJi6Y_D_YZ0DqhDqS=LQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Vincenzo Romano | 2017-07-27 11:27:58 | RETURNS SETOF RECORD with 1 column |
Previous Message | vinny | 2017-07-27 10:05:37 | Re: Row based permissions: at DB or at Application level? |