Prepared Statement doesn't recognize any parameters on Postgres JS binding

Question

I'm trying to make an update for multiple rows at once. For this, I'm making the folowing prepared statement :

WITH new_values (id, data, email) AS (
  VALUES ($1, $2, $3), ($4, $5, $6), ($7, $8, $9), ($10, $11, $12)
)
UPDATE prospects
  SET 
    data = new_values.data,
    email = new_values.email
  FROM new_values
  WHERE
    prospects.id = new_values.id
  RETURNING *

But I'm getting the following error :

error: bind message supplies 12 parameters, but prepared statement "prospects-multi-update" requires 0
api-prospects_1       |     at Connection.parseE (/usr/src/app/node_modules/pg/lib/connection.js:546:11)
api-prospects_1       |     at Connection.parseMessage (/usr/src/app/node_modules/pg/lib/connection.js:371:19)
api-prospects_1       |     at Socket.<anonymous> (/usr/src/app/node_modules/pg/lib/connection.js:114:22)
api-prospects_1       |     at Socket.emit (events.js:180:13)
api-prospects_1       |     at addChunk (_stream_readable.js:274:12)
api-prospects_1       |     at readableAddChunk (_stream_readable.js:261:11)
api-prospects_1       |     at Socket.Readable.push (_stream_readable.js:218:10)
api-prospects_1       |     at TCP.onread (net.js:581:20)

I have tried different queries (with INSERT ... ON CONFLICT DO UPDATE) but it does the same.

It's working only on straight INSERT INTO queries.

EDIT

Here is the code calling this query : (I have remove the string generation functions since it's not very useful)

const upsertValues = async (entities) => {
  if (entities.length === 0) return []

  const client = pool.connect()

  const values = [...]
  const variables = [...]
  const setStatements = [...]

  const res = await client.query({
    name: `${resourceName}-multi-update`,
    test: `
      WITH new_values (id, ${columns}) AS (
        VALUES ${variables}
      )
      UPDATE prospects
        SET ${setStatements}
        FROM new_values
        WHERE
          prospects.id = new_values.id
        RETURNING *
    `,
    values: flatten(values),
  })

  return res.row || []
}

You should include the code you used for executing the query. Most likely the problem is somewhere in there. — vitaly-t
– vitaly-t, Commented May 1, 2018 at 11:30
@vitaly-t I have edited with the code on the call site. I have juste removed the generation of the diferent parts of the query since it's just string manipulation and the generated query is corect. — Emrys Myrooin
– Emrys Myrooin, Commented May 3, 2018 at 13:47
The query must be passed in as property text, and not test. — vitaly-t
– vitaly-t, Commented May 3, 2018 at 15:22
@vitaly-t You can answer the question if you want me to accept it. — Emrys Myrooin
– Emrys Myrooin, Commented May 10, 2018 at 12:55

vitaly-t · Accepted Answer · 2018-05-10 12:57:24Z

1

The error is due to property text of the prepared statement being misnamed as test, so the engine cannot even find the query string.

answered May 10, 2018 at 12:57

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

Prepared Statement doesn't recognize any parameters on Postgres JS binding

EDIT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

EDIT

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related