Re: Efficient use of ON CONFLICT DO UPDATE with the PostgreSQL JDBC driver

Christopher BROWN schrieb am 13.01.2017 um 15:01:
> I've at last had an opportunity to use PostgreSQL 9.6 (instead of
> 9.4) for a project, and have been able to use the ON CONFLICT DO
> UPDATE clause. As it can involve repeating parameter values
> (including, as is my case, large binary files), I'm wondering if
> there's any performance or resource-usage side effects (or
> workarounds) when doing something like this:
>
> INSERT INTO foo (c_uuid, file_data, file_name) VALUES (?, ?, ?)
> ON CONFLICT (c_uuid) DO UPDATE SET file_data = ?, file_name = ?
>
> On the Java side, that means invoking PreparedStatement::setBinaryStream TWICE for the same data.
>
> Is there a better approach?

Maybe I'm missing something but you don't need to the parameters in the UPDATE part:

INSERT INTO foo (c_uuid, file_data, file_name) VALUES (?, ?, ?)
ON CONFLICT (c_uuid) DO
UPDATE SET file_data = excluded.file_data,
file_name = excluded.file_name;

This assumes that in case of an update you actually want to use the same values as provided in the VALUES () clause