postgresql 使用 pg-promise 插入多条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36233566/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting multiple records with pg-promise
提问by Mandy
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:
我有一个需要插入多条记录的场景。我有一个表结构，如 id（它来自其他表）、键（字符）、值（字符）。需要保存的输入将是上述数据的数组。示例：我有一些数组对象，例如：
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);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.
在 MS SQL 中，我会创建 TVP 并通过它。我不知道如何在 postgres 中实现。所以现在我想要做的是使用 pg-promise 库将列表中的所有项目保存在 postgres sql 中的单个查询中。我无法从文档中找到任何文档/理解。任何帮助表示赞赏。谢谢。
回答by vitaly-t
I am the author of pg-promise.
我是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.
有两种方法可以插入多条记录。第一种，也是最典型的方法是通过事务，以确保所有记录都正确插入，或者没有插入。
With pg-promiseit is done in the following way:
使用pg-promise可以通过以下方式完成：
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
});
You initiate a transaction with method tx, then create all INSERTquery promises, and then resolve them all as a batch.
您使用tx方法启动事务，然后创建所有INSERT查询承诺，然后将它们全部解析为批处理。
The second approach is by concatenating all insert values into a single INSERTquery, which I explain in detail in Performance Boost. See also: Multi-row insert with pg-promise.
第二种方法是将所有插入值连接到一个INSERT查询中，我在Performance Boost 中对此进行了详细解释。另请参阅：使用 pg-promise 进行多行插入。
For more examples see Tasksand Transactions.
Addition
添加
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.
值得指出的是，在大多数情况下，我们不会插入记录id，而是自动生成记录。有时我们想要取回新的 id-s，而在其他情况下我们并不关心。
The examples above resolve with an array of null-s, because batchresolves with an array of individual results, and method noneresolves with null, according to its API.
根据其 API，上述示例使用null-s数组解析，因为批处理使用单个结果数组解析，而方法none 使用解析null。
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:
假设我们想要生成新的 id-s，并且想要将它们全部取回。为此，我们将代码更改为以下内容：
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
});
i.e. the changes are:
即变化是：
- we do not insert the
idvalues - we replace method nonewith one, to get one row/object from each insert
- we append
RETURNING idto the query to get the value - we add
a => +a.idto do the automatic row transformation. See also pg-promise returns integers as stringsto understand what that+is for.
- 我们不插入
id值 - 我们将方法none替换为one，以从每个插入中获取一行/对象
- 我们附加
RETURNING id到查询以获取值 - 我们添加
a => +a.id做自动行转换。另请参阅pg-promise 以字符串形式返回整数以了解其+用途。
UPDATE-1
更新-1
For a high-performance approach via a single INSERTquery see Multi-row insert with pg-promise.
有关通过单个INSERT查询的高性能方法，请参阅使用 pg-promise 的多行插入。
UPDATE-2
更新-2
A must-read article: Data Imports.
必读文章：数据导入。
