'cannot insert multiple commands into a prepared statement' postgreSQL REST API

Question

I am trying to add a 'post question' function that makes a post request to my Postgres API/database.

This is my function:

 const postQuestion = (req, res) => {

        let postVals = [req.params.id, Object.values(req.body)].flat();
        let p_id = Number(req.params.id);
        let q_body = postVals[1]
        let q_date = postVals[2];
        let name = postVals[3];
        let help = postVals[4];
        let reported = postVals[5];
        
        pool.query(`SET search_path TO qa, public; \
        INSERT INTO questions(product_id, question_body, question_date, asker_name, helpfulness, reported) \
        VALUES ($1, $2, $3, $4, $5, $6)`, [p_id, q_body, q_date, name, help, reported], (err) => {
          if (err) {
            console.log(err)
          }
          res.send('success')
        })    
      }

And I'm testing it with Postman which is where the req.body comes in from.

This is my schema....

CREATE TABLE questions (
   question_id SERIAL PRIMARY KEY,
   product_id INT,
   question_body VARCHAR(255),
   question_date VARCHAR(255),
   asker_name VARCHAR(255),
   helpfulness INT,
   reported BOOLEAN
);

and I'm getting this error:

error: cannot insert multiple commands into a prepared statement at Parser.parseErrorMessage (/Users/megan/Boulder/SDC/ClarkFECSource/node_modules/pg-protocol/dist/parser.js:278:15)

Try separating the SET and INSERT queries. (note that I found this by googling the error message) — Chris G

Laurenz Albe Laurenz Albe · Accepted Answer · 2020-12-08T07:57:47

The error is caused by the semicolon, or rather, by the fact that your query string contains two statements.

Simply omit the SET search_path and modify the INSERT to INSERT INTO qa.questions.

'cannot insert multiple commands into a prepared statement' postgreSQL REST API

1 Answers