Node.js 中的 PostgreSQL 多行更新 [英] PostgreSQL multi-row updates in Node.js
问题描述
正如我在这里发现的 在 Stackoverflow 上可以通过执行这样的操作来更新一个查询中的多行
As i already found here On Stackoverflow it is possible to update multiple rows in one query by doing something like this
update test as t set
column_a = c.column_a,
column_c = c.column_c
from (values
('123', 1, '---'),
('345', 2, '+++')
) as c(column_b, column_a, column_c)
where c.column_b = t.column_b;
特别感谢@Roman Pekar 的明确答复.
special thanks to @Roman Pekar for the clear answer.
现在我正在尝试将这种更新方式与在 NodeJS 中查询 postgreSQL 数据库结合起来.
Now i'm trying to merge this way of updating with querying to a postgreSQL database in NodeJS.
这是我的代码片段:
var requestData = [
{id: 1, value: 1234}
{id: 2, value: 5678}
{id: 3, value: 91011}
]
client.connect(function (err) {
if (err) throw err;
client.query(buildStatement(requestData), function (err, result) {
if (err) throw err;
res.json(result.rows);
client.end(function (err) {
if (err) throw err;
});
});
});
var buildStatement = function(requestData) {
var params = [];
var chunks = [];
for(var i = 0; i < requestData.length; i++) {
var row = requestData[i];
var valuesClause = [];
params.push(row.id);
valuesClause.push('$' + params.length);
params.push(row.value);
valuesClause.push('$' + params.length);
chunks.push('(' + valuesClause.join(', ') + ')');
}
return {
text: 'UPDATE fit_ratios as f set ratio_budget = c.ratio_budget from (VALUES ' + chunks.join(', ') + ') as c(ratio_label, ratio_budget) WHERE c.ratio_label = f.ratio_label', values: params
}
}
我没有收到错误,但它没有更新我的表,我真的不知道这里出了什么问题.也许我的查询代码中有语法错误?我只是在 NodeJS pg 包中更新时没有找到多行查询的任何具体示例
i don't get an error but it doesn't update my table, i don't really know what goes wrong here. Perhaps a syntax error in my query code? I just don't find any specific examples of multiple row querying when updating in NodeJS pg package
推荐答案
以下示例基于库 pg-promise 及其方法 helpers.更新:
The example below is based on library pg-promise, and its method helpers.update:
// library initialization, usually placed in its own module:
const pgp = require('pg-promise')({
capSQL: true // capitalize all generated SQL
});
const db = pgp(/*your connection details*/);
// records to be updated:
const updateData = [
{id: 1, value: 1234},
{id: 2, value: 5678},
{id: 3, value: 91011}
];
// declare your ColumnSet once, and then reuse it:
const cs = new pgp.helpers.ColumnSet(['?id', 'value'], {table: 'fit_ratios'});
// generating the update query where it is needed:
const update = pgp.helpers.update(updateData, cs) + ' WHERE v.id = t.id';
//=> UPDATE "fit_ratios" AS t SET "value"=v."value"
// FROM (VALUES(1,1234),(2,5678),(3,91011))
// AS v("id","value") WHERE v.id = t.id
// executing the query:
await db.none(update);
这种生成多行更新的方法可以表征为:
This method of generating multi-row updates can be characterized as:
- 非常快,因为它依赖于 ColumnSet为查询生成实现智能缓存
- 完全安全,因为所有数据类型都经过库的查询格式化引擎,以确保所有内容都正确格式化和转义.
- 非常灵活,得益于先进的ColumnConfig 语法支持列定义.
- 非常易于使用,因为 pg-promise 实现了简化的界面.
- very fast, as it relies on type ColumnSet that implements smart caching for query generation
- completely safe, as all data types are going through the library's query formatting engine to make sure everything is formatted and escaped correctly.
- very flexible, due to advanced ColumnConfig syntax supported for the columns definition.
- very easy to use, due to the simplified interface implemented by pg-promise.
请注意,我们在 id
列前面使用了 ?
表示该列是条件的一部分,但不会被更新.有关完整的列语法,请参阅 class Column 和 ColumnConfig 结构.
Note that we use ?
in front of column id
to indicate that the column is part of the condition, but not to be updated. For complete column syntax see class Column and ColumnConfig structure.
相关问题:多行插入与 pg-promise.
这篇关于Node.js 中的 PostgreSQL 多行更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!