I have an application that writes to a PostgreSQL database. I would like to do batch upserts of the data with INSERT ON CONFLICT DO UPDATE. The application needs to be optimized for writes for high traffic. Doing upserts would allow me to simply insert or update all data in one go.

But my problem is that the items in the batch do not always have the same columns set. So setting column values in the DO UPDATE SET… part becomes tricky. I cannot just set to EXCLUDED. because that data may not be present.

Is there a way to solve this issue?

I tried doing SET column = COALESCE(EXCLUDED.column, table_name.column). This works fine except that in the INSERT VALUES part I need to set values to DEFAULT for columns that a particular item does not have values for.

So the statement is something like

INSERT INTO table (column, other_column) VALUES (?, ?), (DEFAULT, ?) ON CONFLICT DO UPDATE SET column = COALESCE(EXCLUDED.column, table.column), other_column = COALESCE(EXCLUDED.other_column, table.other_column);

This does not allow for setting values to NULL as the COALESCE would take the value from the table. But this is fine because the columns do not need to ever be set to NULL.

The problem is if column has a default value set in table schema. Then on the second pair of values the EXCLUDED.column has the value of that column default due to value being set to DEFAULT.

Any suggestions as to how to get around this?

How else would you solve needing to insert or update data with varying columns in a way that is as simple as possible and optimized for writes?