Re: upsert: is there a shortcut?

Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)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