Syntax shorthand for updating only changed rows in UPSERT

Question

I have a query which upserts into Postgres when a key conflicts only if there are changes to one of the other columns (to avoid unnecessary inserts and returned rows where nothing was actually changed):

INSERT INTO public.test_upsert (some_id, a, b, note) 
VALUES 
  ('c', 1, true, 'asdf')
ON CONFLICT (some_id)
DO UPDATE SET 
  a = excluded.a, 
  b = excluded.b, 
  note = excluded.note 
WHERE
  test_upsert.a IS DISTINCT FROM excluded.a OR 
  test_upsert.b IS DISTINCT FROM excluded.b OR
  test_upsert.note IS DISTINCT FROM excluded.note
RETURNING *;

My question is: is there shorthand for this? I basically want to either insert a new record update the rows when any of the columns being inserted are distinct from the existing columns. Writing out each column individually gets quite verbose, although it is more explicit.

Answer 1

There is no provision in SQL to say "all columns except this one". (You can do something like that with jsonb or hstore.) Related answer with details:

• Select columns inside json_agg

On second thought, you don't need this. You might as well update all columns. Postgres writes a new row version anyway, nothing lost.

INSERT INTO test_upsert AS t (some_id, a, b, note)  -- list is optional
VALUES ('c', 5, true, 'asdf')
ON     CONFLICT (some_id)
DO     UPDATE
SET    (some_id, a, b, note) = ROW (excluded.*)  -- ROW syntax
WHERE  (t.*) IS DISTINCT FROM (excluded.*)       -- again, compare whole row
RETURNING *;

You still have to spell out the column list once for the target list of SET. That's required. The rest can be shortened.

You might even omit the target list for the INSERT while targeting all columns anyway, but in most cases it's good form to spell it out, more robust against future changes.

excluded represents the whole row proposed for insertion. The manual:

Note that the effects of all per-row BEFORE INSERT triggers are reflected in excluded values, since those effects may have contributed to the row being excluded from insertion.

Columns that were not targeted explicitly (or in BEFORE INSERT triggers) hold their respective DEFAULT values (NULL being the default DEFAULT). You may need special treatment for serial or IDENTITY columns.

The table alias is an optional additional syntax shorthand. Note that the AS key word is required for a table alias for the target of an INSERT - unlike most places for a table alias.

• The manual on INSERT.

Or even shorter, yet:

...
WHERE  t IS DISTINCT FROM excluded
...

We can as well just use the table name (or alias) for the (well-known!) composite type. The only corner-case disadvantage of this: if there is also a column of the same name "t" (or "excluded"), it takes precedence due to Postgres syntax rules. (t.*) IS DISTINCT FROM (excluded.*) cannot be mistaken and is the slightly safer form. Related answer:

• Convert a row to a nested JSON document containing all relations