Node.js中的PostgreSQL多行更新 [英] PostgreSQL multi-row updates in Node.js
问题描述
我已经在这里找到在Stackoverflow 上通过执行类似的操作，可以在一个查询中更新多行
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的明确回答.
现在，我正在尝试将这种更新与查询合并到NodeJS中的postgreSQL数据库中.
这是我的代码的一部分:
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包中更新时，我只是找不到任何特定的多行查询示例
以下示例基于库 pg-promise 及其方法 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:
db.none(update)
.then(()=> {
// success;
})
.catch(error=> {
// error;
});
这种生成多行更新的方法的特点是:
- 非常快，因为它依赖于 ColumnSet 实现智能缓存以生成查询
- 完全安全，因为所有数据类型都通过库的查询格式引擎来确保所有格式都正确格式化和转义.
- 非常灵活，因为支持高级 ColumnConfig 语法列定义.
- 由于 pg-promise 实现的简化界面，因此非常易于使用.
请注意，我们在列id前面使用?表示该列是条件的一部分，但不进行更新.有关完整的列语法，请参见类列和具有pg-promise的多行插入. /p>
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;
special thanks to @Roman Pekar for the clear answer.
Now i'm trying to merge this way of updating with querying to a postgreSQL database in NodeJS.
Here is a snipped of my code:
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
}
}
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
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:
db.none(update)
.then(()=> {
// success;
})
.catch(error=> {
// error;
});
This method of generating multi-row updates can be characterized as:
- 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.
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.
Related question: Multi-row insert with pg-promise.
这篇关于Node.js中的PostgreSQL多行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
