1

Database create

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

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

Update database data in node

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)

Error

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

If changing $1 $2 $3 $4 to 1 20 2 20, the query worked correctly, but I want to use a prepared statement for it, please help.

GG program
  • 127
  • 1
  • 1
  • 11

1 Answers1

0

I checked your prepared statement code and found nothing wrong, other than the multiline string for the update, which looks wrong. You may try using either the backticks version:

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`;

Or for Node versions earlier than 4+ (ES6) you may use:

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";
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • I am using node v14.17.0, postgres 14.1. Have tried to use backticks but still same error. – GG program Mar 22 '22 at 07:12
  • @GGprogram The problem is that postgresql is unable to correctly infer parameter types for the prepared statement of this kind, thinking that all of them are text, thus resulting in an error. So it is basically a postgresql problem asking if there's way to implement similar functionality while don't having to do explicit casts. – std4453 Jul 04 '22 at 17:14
  • @std4453 do you got any solution? prepared statement is much more secure. – GG program Jul 14 '22 at 14:09
  • @GGprogram unfortunately I eventually specified the type of every element which is not great by all means. Meanwhile maybe it would be a better solution to use an intermediate table like in https://stackoverflow.com/a/37656780 Should give it a try. – std4453 Jul 15 '22 at 17:34