如何使用node-postgres将多行正确插入PG? [英] How do I properly insert multiple rows into PG with node-postgres?
问题描述
可以像这样插入一行:
client.query("insert into tableName (name, email) values ($1, $2) ", ['john', 'john@gmail.com'], callBack)
这种方法会自动注释掉任何特殊字符。
This approach automatically comments out any special characters.
如何一次插入多行?
我需要实现这一点:
"insert into tableName (name, email) values ('john', 'john@gmail.com'), ('jane', 'jane@gmail.com')"
我只能使用js字符串运算符手动编译此类行,但随后我需要以某种方式添加特殊字符转义符。
I can just use js string operators to compile such rows manually, but then i need to add special characters escape somehow.
推荐答案
以下文章: 性能增强来自 pg-promise 库及其建议的方法:
Following this article: Performance Boost from pg-promise library, and its suggested approach:
// Concatenates an array of objects or arrays of values, according to the template,
// to use with insert queries. Can be used either as a class type or as a function.
//
// template = formatting template string
// data = array of either objects or arrays of values
function Inserts(template, data) {
if (!(this instanceof Inserts)) {
return new Inserts(template, data);
}
this._rawDBType = true;
this.formatDBType = function () {
return data.map(d=>'(' + pgp.as.format(template, d) + ')').join(',');
};
}
与您的情况完全相同的使用示例:
An example of using it, exactly as in your case:
var users = [['John', 23], ['Mike', 30], ['David', 18]];
db.none('INSERT INTO Users(name, age) VALUES $1', Inserts('$1, $2', users))
.then(data=> {
// OK, all records have been inserted
})
.catch(error=> {
// Error, no records inserted
});
它也适用于一系列对象:
And it will work with an array of objects as well:
var users = [{name: 'John', age: 23}, {name: 'Mike', age: 30}, {name: 'David', age: 18}];
db.none('INSERT INTO Users(name, age) VALUES $1', Inserts('${name}, ${age}', users))
.then(data=> {
// OK, all records have been inserted
})
.catch(error=> {
// Error, no records inserted
});
UPDATE-1
有关通过单个 INSERT
查询的高性能方法,请参见具有pg-promise的多行插入。
For a high-performance approach via a single INSERT
query see Multi-row insert with pg-promise.
UPDATE-2
这里的信息现在已经很旧了,请参见自定义类型格式。以前是 _rawDBType
的现在是 rawType
和 formatDBType
被重命名为 toPostgres
。
The information here is quite old now, see the latest syntax for Custom Type Formatting. What used to be _rawDBType
is now rawType
, and formatDBType
was renamed into toPostgres
.
这篇关于如何使用node-postgres将多行正确插入PG?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!