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) 
  ('c', 1, true, 'asdf')
ON CONFLICT (some_id)
  a = excluded.a, 
  b = excluded.b, 
  note = excluded.note 
  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

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.

1 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:

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 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.

Or even shorter, yet:


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:

  • This is amazing! Thanks! Much more succinct yet still explicit as to what's happening. :-) Having to type the columns twice instead of 5 times will save my fingers a lot of strain since I have to do this for a number of tables. :D
    – Caustic
    Commented Dec 27, 2020 at 15:10
  • 1
    @Caustic: While inserting to all (or leading) columns, you might even omit the target list of INSERT, too. Typically, it's better to be explicit there, though. Commented Dec 27, 2020 at 15:19
  • Thanks, @ErwinBrandstetter ! Reason to specify which columns are updated: a trigger BEFORE UPDATE OF note ON table shouldn't trigger if note didn't change
    – Marius
    Commented Nov 1, 2022 at 12:58
  • 1
    @Marius: Be aware that this clause only demands the column be mentioned as target. To make sure it actually changed, add a WHEN clause. Example: stackoverflow.com/a/18360493/939860 Commented Nov 1, 2022 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.