From: | Thomas Kellerer <spam_eater(at)gmx(dot)net> |
---|---|
To: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: Efficient use of ON CONFLICT DO UPDATE with the PostgreSQL JDBC driver |
Date: | 2017-01-13 19:17:13 |
Message-ID: | o5b93k$6ps$1@blaine.gmane.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Wooten | 2017-01-15 23:20:32 | JDBC String to Bool spec |
Previous Message | Brad DeJong | 2017-01-13 17:32:07 | Re: Efficient use of ON CONFLICT DO UPDATE with the PostgreSQL JDBC driver |