PostgreSQL multi-value upserts

Question

is it possible to perform a multi-value upsert in PostgreSQL? I know multi-value inserts exist, as do the "ON CONFLICT" key words to perform an update if the key is violated... but is it possible to bring the two together? Something like so...

INSERT INTO table1(col1, col2) VALUES (1, 'foo'), (2,'bar'), (3,'baz')
ON CONFLICT ON CONSTRAINT theConstraint DO
UPDATE SET (col2) = ('foo'), ('bar'), ('baz')

I googled the crud out of this and couldn't find anything on regarding it.

I have an app that is utilizing pg-promise and I'm doing batch processing. It works but its horrendously slow (like 50-ish rows every 5 seconds or so...). I figured if I could do away with the batch processing and instead correctly build this multi-valued upsert query, it could improve performance.

Edit: Well... I just tried it myself and no, it doesn't work. Unless I'm doing it incorrectly. So now I guess my question has changed to, what's a good way to implement something like this?

Answer 1

Multi-valued upsert is definitely possible, and a significant part of why insert ... on conflict ... was implemented.

CREATE TABLE table1(col1 int, col2 text, constraint theconstraint unique(col1));

INSERT INTO table1 VALUES (1, 'parrot'), (4, 'turkey');

INSERT INTO table1 VALUES (1, 'foo'), (2,'bar'), (3,'baz')
ON CONFLICT ON CONSTRAINT theconstraint
DO UPDATE SET col2 = EXCLUDED.col2;

results in

regress=> SELECT * FROM table1 ORDER BY col1;
 col1 | col2 
------+------
    1 | foo
    2 | bar
    3 | baz
    4 | turkey
(4 rows)

If the docs were unclear, please submit appropriate feedback to the pgsql-general mailing list. Or even better, propose a patch to the docs.