执行查询时出错 {"statusCode"：500，"error"："内部服务器错误"，"消息"："语法错误在 \")\" "} 处或附近出现错误

Question

我有一个主键地址的数据库

| Address               | other values...|
| ----------------------| -------------- |
| ed.nl                 | null           |
| spelletjes.nl         | null           |
| kinderspelletjes.nl   | null           |

我想写一个请求，删除与我在数组中传递的地址匹配的行。

我有这个在我的missing-urls.js文件

async function deleteMissingUrls(request, reply) {
try {
const addresses = request.body;
const { rows } = await pg.query(formatQry.deleteByAddress, [addresses]);
reply.send({
total: rows.length,
items: rows,
});
} catch (err) {
log.error(`Error while deleting. ${err.message}`);
reply.internalServerError(err.message);
}
}

在我的query/missing-urls.js中使用这个查询文件

export const deleteByAddress = 'DELETE FROM metrics.missing_url WHERE address IN (?)';

当我运行这个请求时

curl -X DELETE -d '["ed.nl", "spelletjes.nl"]' -H "Content-Type: application/json" http://localhost:3000/api/missing/urls

出现错误。

{"statusCode":500,"error":"Internal Server Error","message":"syntax error at or near ")""}%

我做错了什么?

Answer 1

您应该使用任意运算符column = any(?)

如果你使用node-postgres驱动程序，你的代码看起来像这样:

await pg.quert('DELETE FROM metrics.missing_url WHERE address = ANY($1)', [['address 1', 'address 2', ...]])