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?

Stumpage

Possible duplicate of PostgreSQL multi-row updates in Node.js – Pickmeup

Thanks, @vitaly-t. I'm going to use some of these templates in conjunction with building out the queries in the answer below. I was missing the important "EXCLUDED" portion of the upsert. Thanks for that pg-promise library man... been using it extensively and it's absolutely fantastic!!! – Stumpage

This one is perhaps closer to your question: Multi-row insert with pg-promise. – Pickmeup

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.

Ragouzis

Awesome, thank you so much!! In my folly I didn't examine the special "EXCLUDED" expression. Found this in the postgres wiki... though a bit buried in the ON CONFLICT documentation: -- Multi-row insert-or-update, with reference to rejected tuples using special EXCLUDED.* expression: – Stumpage

Answer 2

1.Before the insert

2.Command

3.After the insert

Tupper