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

Christopher Brown wrote:
> ... I'd like to avoid sending the same data twice ...
> ... 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 = ?
> ... The SQL syntax (and the query workflow, and parameter order) is specific to PostgreSQL,
> so I'm not against something that varies from the basic "PreparedStatement" syntax.
> If there's a safe way of figuring out the intention, that'd be excellent. ...

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

INSERT 0 1

Query returned successfully in 182 msec.