PostgreSQL多值upserts [英] PostgreSQL multi-value upserts

问题描述

是否可以在PostgreSQL中执行多值upsert？我知道存在多值插入，如果键被违反， ON CONFLICT关键字也可以执行更新...但是可以将两者组合在一起吗？像这样...

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.

我有一个利用pg-promise的应用程序，并且正在做批处理。它可以工作，但是速度极其慢（每5秒左右有50条左右的行……）。我想出是否可以取消批处理，而是正确构建此多值upsert查询，可以提高性能。

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.

编辑：
好​​。 ..我只是自己尝试过，不，它不起作用。除非我做错了。所以现在我想我的问题变成了，实现这样的一种好方法是什么？

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?

推荐答案

多值upsert是

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;

结果

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

如果文档不清楚，请向pgsql一般邮件列表。甚至更好，为文档提出补丁。

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

这篇关于PostgreSQL多值upserts的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！