Thread: [GENERAL] upsert: is there a shortcut?

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

Daniele Varrazzo <daniele.varrazzo@gmail.com> wrote:
>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).

This is certainly something that I've seen requests for before. I tend
to think that using such a feature would be a bit like using "SELECT *"
in production: something that provides an immediate convenience, but
creates unforeseen problems.

As an example, imagine if someone adds an "inserted_at" column, which
has "now()" as its default value. Today, a user can be pretty confident
that no existing or future query is going to change that itself, because
in order for that to happen the query would have to be written with the
explicit intention of updating "inserted_at". That property would go
away with the feature you describe. Subtleties like this could easily be
missed.

--
Peter Geoghegan