[JDBC] Re: Efficient use of ON CONFLICT DO UPDATE with the PostgreSQL JDBCdriver

Brad DeJong schrieb am 13.01.2017 um 18:18:
> I find it easiest to make the intention explicit like this. This is
> also a more conventient syntax for handling multiple rows because you
> just extend the "values (?, ?, ?)" to values "(?, ?, ?), (?, ?, ?),
> (?, ?, ?)".
>
>     with params (c_uuid, file_data, file_name) as (values (?, ?, ?))
>     insert into foo (c_uuid, file_data, file_name)
>     select c_uuid, file_data, file_name from params
>     on conflict (c_uuid) do update set (file_data, file_name) = (select file_data, file_name from params)
>
>
> I ran this statement in 9.6 through pgAdmin 4 in order to verify the
> syntax (with the ?, ?, ? replaced with literals - I have not yet
> figured out how to bind parameters to parameterized statements in
> pgAdmin 4) and the messages were ...

That is overly complicated because you can do a simple:

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;

Plus: using EXCLUDED means this will also work for multi-row inserts which your workaround does not:


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;