我使用NodeJS和PG以及一个PostgreSQL数据库作为后端.我想在一个语句中向一个表插入多行,使用id(它是BIGSERIAL主键NOT NULL)来判断它是否是要插入而不是更新的新记录.
如果我创建查询,只需像这样传入值,它就可以工作:
let upsertQuery = `INSERT INTO triage (id, col1, col2)
VALUES (1, 'abc', 'def'), (DEFAULT, 'ghi', 'jkl')
ON CONFLICT (id) DO UPDATE SET
col1 = EXCLUDED.col1,
col2 = EXCLUDED.col2`;
但如果我使用参数来避免SQL注入,如下所示:
let upsertQuery = `INSERT INTO triage (id, col1, col2)
VALUES ($1, $2, $3), ($4, $5, $6)
ON CONFLICT (id) DO UPDATE SET
col1 = EXCLUDED.col1,
col2 = EXCLUDED.col2`;
let values = [1, 'abc', 'def', 'DEFAULT', 'ghi', 'jkl']
try {
await pool.query(upsertQuery, values)
res.status(201).json({message:'Updated'});
}
catch(err) {
await pool.query('ROLLBACK');
console.log(JSON.stringify(err, null, 2));
}
错误是:
"name":"错误", "Severity":"错误", "code":"22P02", "WHERE":"未命名门户参数$4=‘...’", "file":"numutils.c", "rouble":"pg_strtoint64"