There is no provision in SQL to say "all columns except this one". (You can do something like that with
hstore.) Related answer with details:
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)
SET (some_id, a, b, note) = ROW (excluded.*) -- ROW syntax
WHERE (t.*) IS DISTINCT FROM (excluded.*) -- again, compare whole row
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
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
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.
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: