使用pg-promise助手进行多行插入/更新时的INSERT ON CONFLICT DO UPDATE

Question

我正尝试使用pg-promise将多行数据插入到pgsql数据库中。在我的情况下，我尝试插入的几条记录可能已经存在于表中。在这种情况下，我需要更新它们。通过查阅pg-promise官方文档，我发现可以使用helpers插入或更新多行数据。是否有办法像下面这样进行多个插入/更新？

INSERT INTO table_name(column_list) 
VALUES(value_list)
ON CONFLICT target action;

- Giri

---

是的，只需使用辅助程序创建插入值查询，然后将 ON CONFLICT … 作为字符串附加即可。 - Bergi

@Bergi 是的，基本上是这样。我还是添加了我的答案，也许会更有帮助 :) - vitaly-t

@vitaly-t 是啊，但我不知道它会看起来是什么样子，而且我也不知道 assignColumns！ - Bergi

Answer 1

在某处创建您的静态变量：

const cs = new pgp.helpers.ColumnSet(['first', 'second', 'third', 'fourth'], 
                                      {table: 'my-table'});

// let's assume columns 'first' and 'second' produce conflict when exist:
const onConflict = ' ON CONFLICT(first, second) DO UPDATE SET ' +
    cs.assignColumns({from: 'EXCLUDED', skip: ['first', 'second']});

• 请参阅ColumnSet和assignColumns。

在下面的示例中，我们假设您的data是有效对象的数组：

const upsert = pgp.helpers.insert(data, cs) + onConflict; // generates upsert

await db.none(upsert); // executes the query:

额外功能

如果您希望所有生成的 SQL 语句均为大写而不是小写，则可以设置选项 capSQL：

const pgp = require('pg-promise')({
  capSQL: true
});

最好的方法是借助pg-monitor来查看生成和执行的内容。

- vitaly-t

---

如果您的更新依赖于当前值怎么办？ 比如 first = my-table.first + EXCLUDED.first 您应该将其作为onConflict查询的一部分添加还是可以使用assignColumns做些什么？ - Marius Mircea

@MariusMircea 您只需要手动添加，assignColumns 只是为了简化常规赋值，因为它涵盖了99%的实际情况。而您所要求的是相当独特的。 - vitaly-t

完美运作！对于像我这样对“EXCLUDED”使用感到困惑的人来说，它是用于访问我们未能成功插入的行的Postgres语法。https://www.postgresql.org/docs/current/sql-insert.html - anotherfred

有没有办法返回更新的行数和创建的行数？我看到使用 db.result() 我们可以得到 rowCount，但是没有指定哪些列会发生冲突。 - user3770935

谢谢，伙计，这太棒了！我以前从未使用过pg，但这太棒了！ - B.Ramburn