用pg-promise插入多个记录 [英] Inserting multiple records with 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 值


• 我们将方法无替换为<一个href = http://vitaly-t.github.io/pg-promise/Database.html#one rel = noreferrer>一个，从每个插入中获取一行/对象
>

• 我们在查询中附加 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

必读文章：数据导入。

这篇关于用pg-promise插入多个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！