Got an error while executing query {“statusCode”:500,”error”:”Internal Server Error”,”message”:”syntax error at or near “)””}

I have a db with a primary key address

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

I want to write a request that would delete rows that match the address that I pass in the array.

I have this in my missing-urls.js file

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);
  }
}

and use this query in my query/missing-urls.js file

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

When I run this request

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

I got an error.

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

What I am doing wrong?

Answer

You should use any operator column = any(?)

If you use node-postgres driver your code look like this:

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

Advertisement

Answer