How to fix multi row node-postgres insert - Syntax Error at or near $4

Question

I have a multi row node-postgres insert that is giving me issues. It is a parameterized query that uses a Common Table Expression to update a primary table and two tables with foreign keys. The query works with the parameters to the primary table, but, when I try to parameterize the foreign key tables, which are multi row, it throws the syntax error.

First, I have this function that takes an array and returns a string of values.

const buildValues = (id, values) => {
    return values 
        .map(val => "(" + id + ", '" + val + "', " + false + ")")
        .join(", ");
    };

Here is my query:

app.post('/api/saveperson', function (req, res) {
  pg.connect(connectionString, (err, client, done) => {

    const insertPerson = `
    WITH x AS ( INSERT INTO people (title, notes, name)
        VALUES ($1, $2, $3)
        RETURNING personId
    ),
    b AS (
        INSERT INTO sports (personid, name, favorite)
        VALUES $4 )
    INSERT INTO instructions (personid, name, favorite)
    VALUES $5; 
    `;

      client.query(insertPerson, 
            [ req.body.title
            , req.body.notes
            , req.body.name
            , queries.buildValues("SELECT personId FROM x", req.body.sports)
            , queries.buildValues("SELECT personId FROM x", req.body.instructions)
            ]
         )
          .then( () => client.end())
          .catch( err => console.log(err));
  });
  return res.json(req.body);
});

the number of columns to insert to sports is 3 yet only one value ($4), same for instructions... — Vao Tsun
– Vao Tsun, Commented Apr 11, 2017 at 7:39
Is there a better way to approach this problem? I feel like using a function to iterate out (e.g., $4, $5, $6...) for two arrays of unknown length could get ugly. — Matt
– Matt, Commented Apr 11, 2017 at 18:08
try changing client.query(insertRecipe in code above to client.query(insertPerson — Vao Tsun
– Vao Tsun, Commented Apr 11, 2017 at 20:01
and the error is next to $4, not $5?.. please update question with exact quote of error — Vao Tsun
– Vao Tsun, Commented Apr 11, 2017 at 20:10

vitaly-t · Accepted Answer · 2017-04-22 19:50:58Z

1

Not sure if this is the best solution, but I ended up using pg-promise library instead of just pg. Then I use a transaction and a chained query:

  db.tx(t => {
      // BEGIN has been executed
      return t.one(
          `INSERT INTO people (title, notes, name) 
          VALUES ($[title], $[notes], $[name]) 
          RETURNING personid`, req.body)
          .then(data => {
            let sportsQueries = req.body.sports.map(sport => {
              return t.none(`INSERT INTO sports (personid, name) 
                          VALUES ($1, $2)`, [data.personid, sport]);     
            });

            let instructionsQueries = req.body.instructions.map(ins => {
              return t.none(`INSERT INTO instructions (personid, instruction) 
                          VALUES ($1, $2)`, [data.personid, ins]);
            });

            return t.batch(sportsQueries.concat(instructionsQueries));

          });
  })
     .then(data => {
         // Success, and COMMIT has been executed
     })
     .catch(error => {
         // Failure, and ROLLBACK has been executed
     })

edited Apr 22, 2017 at 19:50

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

answered Apr 12, 2017 at 1:40

Matt

4,7805 gold badges29 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

vitaly-t Over a year ago

This looks correct. For higher-performance version though, see also: Multi-row insert with pg-promise, i.e. you can generate each set of inserts as a single query, which would make the transaction much faster ;)

Matt Over a year ago

I will look into that. Thank you!

Collectives™ on Stack Overflow

How to fix multi row node-postgres insert - Syntax Error at or near $4

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related