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?

Possible duplicate of PostgreSQL multi-row updates in Node.js — vitaly-t
– vitaly-t, Commented Nov 17, 2016 at 8:08
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!!! — dvsoukup
– dvsoukup, Commented Nov 17, 2016 at 18:28
This one is perhaps closer to your question: Multi-row insert with pg-promise. — vitaly-t
– vitaly-t, Commented Nov 17, 2016 at 21:29

Craig Ringer · Accepted Answer · 2016-11-17 07:43:36Z

13

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.

edited Nov 17, 2016 at 7:43

answered Nov 17, 2016 at 6:58

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dvsoukup Over a year ago

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:

liyuhui · Accepted Answer · 2017-09-15 07:20:14Z

1

1.Before the insert

2.Command

3.After the insert

answered Sep 15, 2017 at 7:20

liyuhui

1,28012 silver badges17 bronze badges

Collectives™ on Stack Overflow

PostgreSQL multi-value upserts

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related