postgresql 如何使用 node-postgres 将多行正确插入 PG？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34990186/
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
How do I properly insert multiple rows into PG with node-postgres?
提问by stkvtflw
A single row can be inserted like this:
可以像这样插入单行：
client.query("insert into tableName (name, email) values (, ) ", ['john', '[email protected]'], callBack)
This approach automatically comments out any special characters.
这种方法会自动注释掉任何特殊字符。
How do i insert multiple rows at once?
如何一次插入多行？
I need to implement this:
我需要实现这一点：
"insert into tableName (name, email) values ('john', '[email protected]'), ('jane', '[email protected]')"
I can just use js string operators to compile such rows manually, but then i need to add special characters escape somehow.
我可以只使用 js 字符串运算符手动编译这些行，但是我需要以某种方式添加特殊字符转义。
采纳答案by vitaly-t
Following this article: Performance Boostfrom pg-promiselibrary, and its suggested approach:
遵循这篇文章：来自pg-promise库的性能提升，及其建议的方法：
// 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 ', Inserts(', ', 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 ', Inserts('${name}, ${age}', users))
.then(data=> {
// OK, all records have been inserted
})
.catch(error=> {
// Error, no records inserted
});
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
The information here is quite old now, see the latest syntax for Custom Type Formatting. What used to be _rawDBTypeis now rawType, and formatDBTypewas renamed into toPostgres.
这里的信息现在已经很旧了，请参阅Custom Type Formatting的最新语法。过去_rawDBType是现在rawType，formatDBType改名为toPostgres.
回答by Sergey Okatov
One other way using PostgreSQL json functions:
使用 PostgreSQL json 函数的另一种方式：
client.query('INSERT INTO table (columns) ' +
'SELECT m.* FROM json_populate_recordset(null::your_custom_type, ) AS m',
[JSON.stringify(your_json_object_array)], function(err, result) {
if(err) {
console.log(err);
} else {
console.log(result);
}
});
回答by Vyacheslav
client.query("insert into tableName (name, email) values (, ),(, ) ", ['john', '[email protected]','john', '[email protected]'], callBack)
doesn't help? Futher more, you can manually generate a string for query:
没有帮助？此外，您可以手动生成用于查询的字符串：
insert into tableName (name, email) values (" +var1 + "," + var2 + "),(" +var3 + ", " +var4+ ") "
if you read here, https://github.com/brianc/node-postgres/issues/530, you can see the same implementation.
如果您在这里阅读https://github.com/brianc/node-postgres/issues/530，您可以看到相同的实现。
