error: operator does not exist: text = integer

[jackhts4]

Fail to update multiple rows in node-pg with prepared statement

CREATE TABLE user(
    id serial primary key,
    point INT
)

INSERT INTO user(point)
VALUES (11),(22),(33);

const { Pool, Client } = require('pg');
const pgPool = new Pool({...});
const query = `
    UPDATE user AS u 
    SET point = u2.point
    FROM(
        VALUES ($1, $2) ($3, $4)
    ) AS u2( id, point)
    WHERE u2.id = u.id;
`
const values = [1, 10, 2, 20]
pg.query(query, values)

(node:4676) UnhandledPromiseRejectionWarning: error: operator does not exist: text = integer

If changing $1 $2 $3 $4 to 1 20 2 20, the query worked correctly, but prepared statement failed

[KristjanTammekivi]

This happens directly in database as well ``` postgres=# prepare updateuser as UPDATE "user" AS u SET point = u2.point FROM( VALUES ($1, $2), ($3, $4) ) AS u2( id, point) WHERE u2.id = u.id; ERROR: operator does not exist: text = integer LINE 6: WHERE u2.id = u.id; ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

As the hint suggests, you'll need to add casts like $1::int, $2::int, etc

[dmackca]

I'm experiencing this too. I tried it in the query config object syntax and got the same error. Explicitly casting the values as ::int as @KristjanTammekivi mentioned does work around the error, but this seems like a bug because the documentation has an example where a numeric parameter is passed in with no casting: https://node-postgres.com/features/queries#prepared-statements

const query = {
  // give the query a unique name
  name: 'fetch-user',
  text: 'SELECT * FROM user WHERE id = $1',
  values: [1],
}
 
const res = await client.query(query)
console.log(res.rows[0])

Edit with a theory: I might expect to see this the other way around: numbers coming from Postgres can be larger than the biggest number allowed in Javascript, so they might come back as a string in a query result. But here I'm using a single-digit number from JS to query a 4-byte integer column 🤷