用pg

本文介绍了用pg-promise插入多个记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个需要插入多个记录的场景。我有一个表格结构，例如id（其他表格中的fk），键（字符），值（字符）。需要保存的输入将是上述数据的数组。例如：我有一些数组对象，例如：

I have a scenario in which I need to insert multiple records. I have a table structure like id (it's fk from other table), key(char), value(char). The input which needs to be saved would be array of above data. example: I have some array objects like:

lst = []; obj = {}; obj.id= 123; obj.key = 'somekey'; obj.value = '1234'; lst.push(obj); obj = {}; obj.id= 123; obj.key = 'somekey1'; obj.value = '12345'; lst.push(obj);

在MS SQL中，我会已创建并通过了TVP。我不知道如何在Postgres中取得成就。 现在，我想做的是使用pg-promise库将列表中的所有项目保存在postgres sql的单个查询中。我找不到任何文档/无法从文档中了解。任何帮助表示赞赏。谢谢。

In MS SQL, I would have created TVP and passed it. I don't know how to achieve in postgres. So now what I want to do is save all the items from the list in single query in postgres sql, using pg-promise library. I'm not able to find any documentation / understand from documentation. Any help appreciated. Thanks.

推荐答案

我是 pg-promise 。

有两种插入多个记录的方法。第一种也是最典型的方法是通过事务，以确保正确插入所有记录，或确保没有插入所有记录。

There are two ways to insert multiple records. The first, and most typical way is via a transaction, to make sure all records are inserted correctly, or none of them.

使用 pg-promise 可以通过以下方式完成：

With pg-promise it is done in the following way:

db.tx(t => { const queries = lst.map(l => { return t.none('INSERT INTO table(id, key, value) VALUES(${id}, ${key}, ${value})', l); }); return t.batch(queries); }) .then(data => { // SUCCESS // data = array of null-s }) .catch(error => { // ERROR });

您可以使用方法 tx ，然后创建所有 INSERT 查询承诺，然后将它们全部解析为批量。

You initiate a transaction with method tx, then create all INSERT query promises, and then resolve them all as a batch.

第二种方法是将所有插入值连接到单个 INSERT 查询中，我在性能提升。另请参见：带有pg-promise的多行插入。

The second approach is by concatenating all insert values into a single INSERT query, which I explain in detail in Performance Boost. See also: Multi-row insert with pg-promise.

有关更多示例，请参见任务和交易 。

For more examples see Tasks and Transactions.

加法

值得指出的是，在大多数情况下，不要插入记录 id ，而是自动生成记录。有时我们想找回新的id，而在其他情况下我们不在乎。

It is worth pointing out that in most cases we do not insert a record id, rather have it generated automatically. Sometimes we want to get the new id-s back, and in other cases we don't care.

上面的示例使用空 -s，因为批量可以解决包含一系列单独的结果，并且方法无解析为 null ，根据其API。

The examples above resolve with an array of null-s, because batch resolves with an array of individual results, and method none resolves with null, according to its API.

让我们假设我们要生成新的id，并且我们想要让他们都回来。为此，我们将代码更改为以下代码：

Let's assume that we want to generate the new id-s, and that we want to get them all back. To accomplish this we would change the code to the following:

db.tx(t => { const queries = lst.map(l => { return t.one('INSERT INTO table(key, value) VALUES(${key}, ${value}) RETURNING id', l, a => +a.id); }); return t.batch(queries); }) .then(data => { // SUCCESS // data = array of new id-s; }) .catch(error => { // ERROR });

即更改如下：

• 我们不插入 id 值
• 我们将方法无替换为一个，从每个插入中获取一行/对象
>
• 我们在查询中附加 RETURNING id 以获取值
• 我们添加 a => + a.id 进行自动行转换。另请参见 pg-promise将整数作为字符串返回，以了解 + 用于。

• we do not insert the id values
• we replace method none with one, to get one row/object from each insert
• we append RETURNING id to the query to get the value
• we add a => +a.id to do the automatic row transformation. See also pg-promise returns integers as strings to understand what that + is for.

UPDATE-1

有关通过单个 INSERT 查询的高性能方法，请参见具有pg-promise的多行插入。

For a high-performance approach via a single INSERT query see Multi-row insert with pg-promise.

UPDATE- 2

必读文章：数据导入。