We have an UPSERT feature achieved through on conflict do set. How can I set the values conditionally in the set statement, like

if(excluded.col1 is null) col1=table.col1 else col1=excluded.col1

We are using Postgres 9.5.

SET   col1 = COALESCE(excluded.col1, table.col1)

Of course, both tables must be visible.

If you are updating table1 and that's the only change you make it's much more efficient to use a WHERE clause:

SET   col1 = excluded.col1
WHERE excluded.col1 IS NOT NULL
AND   excluded.col1 IS DISTINCT FROM table1.col1 -- to avoid empty updates

About the added second condition:

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.