6

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.

9

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

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:

2
  • 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
    Dec 27 '20 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. Dec 27 '20 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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